Excel Power Trick – Save Time Without VBA

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?’

 

The Excel Club

 

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?

Excel Power Trick

 

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

 

google-play-badge learn excel fast

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.

8 thoughts on “Excel Power Trick – Save Time Without VBA

  1. With Power Query finally finally becoming a part of Excel (no add-in anymore) the tool definitely will eliminate many of my VBA scripts used to transform data. I’m now in search of the way the queries can be automated so it’s about time I break down and search that out. Any suggestions on courses, books, or other information that may help me on doing this?

      1. I’ve just run into a problem when trying to add data. I received the error: Expression.Error: The key did not match any rows in the table
        I created another worksheet adding the December data as per your example.
        I Edited the Query and selected the new Workbook with the December figures and then received the above error.
        How can I overcome this please?

Leave a Reply

%d bloggers like this: