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.

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.


Show Horizontal Axis Entries Below the Chart

Great for displaying negatives in a line or column chart

I learned about a chart Axis option in Excel during a recent webinar – thanks to one of the attendees. You can show the Axis entries below the chart – this is handy for column charts that display negatives.

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

Paste Values

After copying, use the following keyboard combination to paste just the values – no formulas or formats.

Alt h v v

These keys are pressed in sequence, not held down.

Excel 2013 Hide Filter Icon in a Table

This tip applies to Excel 2013 only.

If you have used the Format as Table option on a table you now have the ability to turn off the filter icons in the header row without losing the filter that has been applied.

The new Option is in the Table Style Option section of the DESIGN ribbon, see image below.

Filter_1

Related Posts