Distinct Count in Excel

The Data Model to the rescue

Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count.

Waterfall charts

2018-05-15

I prefer to call them Bridge charts rather than Waterfall charts, but Waterfall is the common name.

Excel added Waterfalls in Excel 2016.

I think the name Bridge is more descriptive since a bridge takes you from one place to another which is what the chart does with values.

Waterfalls in nature only fall down, whilst a waterfall chart has measures that rise and fall.

This blog post show many examples. Most examples are not done in Excel.

Link to blog post.

 

Excel and Outliers

New functions make it easier to find them

Malcolm Gladwell’s book Outliers is a great read – I reviewed it here. Its premise is that some outliers (events that are far outside “normal” expectations) have causes and hence are worthy of investigation. Excel have some functions that can help identify outliers in your data.

Creating a table of all combinations in Excel [Video]

Power Query solution

Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.

Export a sheet as a PDF

It takes a few clicks but it is possible

I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)

Relationships Shortcut

Excel 2013 added the Data Model to Excel.

The Relationships option (Data ribbon) is part of that model. It allows you to create relationships between tables so that you can use a PivotTable to report on multiple tables.

You have to have used the formatted table option to use Relationships.

See my December 2016 INTHEBLACK article for an example.

The keyboard shortcut to create or edit, a Relationship is easy to remember – it is

Alt a a

Pressed in sequence, not held down.


Day Of The Week

A quick and easy way to find out the day of the week for a specific date is to use the Long Date format from the drop down in the middle of the Home ribbon (in the Number section) – see below.

DayofWeek