How often have you seen an Excel spreadsheet like the one below? Very often, right? I am sure many of you are even saying ‘ What’s the problem with that spreadsheet?’
But I know that some of you can spot the problems straight away. This matrix style reporting is common in Excel. It contains simple row headers and column headers to identify the elements of the matrix. In this worksheet we have a different for matrix for each sales rep.
But what if you wanted to analyse your report further or presented in a different way. Using visualizations instead of a table of data such as the image below? How would you go from A to B every month as quick as possible?
A lot, if not most of the work is formatting the data and the structure of the worksheet. The matrix for each rep needs to be combined, right?
So you could remove the Page title header, then remove the total rows and also removed blank rows. You would probable fill down the rep and now you would have a combined matrix table of data. Then add some totals. You could create a graph from this combined matrix table of data. But because the formatting is not correct, you would have problems adding slicers. Happy with the graph, the following month we would do the same tasks again when the new file is emailed over.
Or you could create a table from each rep and then use these tables as a different series within a chart. Again you would have problems with adding slicers because of the data formatting. Then update the tables with new data each month from the new file emailed over?
Which method would you have used?
Do you have a better suggestion?
Why don’t you download the data and have a try? Download data for Excel Power Trick here
For years now people have been creating VBA macros to clean up reports and carry out repetitive tasks. Exactly like the one above. Power Query, known as Get and Transform data in Excel 2016, has the ability to replace VBA macros for many formatting and data cleansing tasks. You can then save this query and use it over and over as necessary.
Excel Power Trick
In this Excel Power Trick, you will learn, step by step, how you can create a query to transform data structured in a format that is not efficient for reporting. You will also learn how you can save that query so it can be used again month on month.
Could this be the beginning of the end of VBA macros in Excel? Comment below
Did you try our FREE APP – Learn Excel Fast. Containing our free video course, Basic Excel for Basic Bookkeeping, with activities to complete and a workbook to download.