Archive for May, 2019

Say you want to combine multiple Excel files, but there is a twist. Each file has few tabs (worksheets) and you want to combine like for like, ie , all Sheet1s to one dataset, all Sheet2s to another dataset…

To make matters interesting each sheet has a different format.

What now?

Of course Power Query to the rescue.

Here is a very useful and almost secret Excel tip for you. Imagine you are looking at a big, complex workbook with lots of calculations. You want to understand where everything is pointing to and how the workbook is set up.

You can use trace precedents in Excel to do this. Read this tip to learn how it works.

Learn how to make a Circular Arc Chart in Excel.

For every column chart that is done right, there are a dozen that get messed up. That is why lets talk about 5 simple rules for making awesome column charts.

Tip: Same rules apply for bar charts too.

Time for another Excel formula / Power Query challenge. This is based on a common business data analysis problem. Say you have two tables – calls log and office hours. Call log tells when each call is received. Office hours tell us working hours for seven days of the week. We want to know how many calls are outside office hours.

Recently, Microsoft Power BI introduced a very useful visualization, called key influencers visualization. As the name suggests, this is a chart of key parameters that effect a measure or outcome.

For example, you have customer satisfaction rating as a measure. Now you want to know which aspects of your data impact the ratings most? You can create the key influencer visual and Power BI finds all the top ranking influencers (using rules and machine learning).

But can we make it in Excel?

Let’s see…