Pivot Tables Archives

Pivot Table Tip- Assign The Correct Row And Column Labels Quickly

April 18, 2018 by Barbara

Hello Excellers, today I want to share with you a really good Excel Pivot Table Tip with you. Do you ever quickly create a Pivot Table, then end up with really naff labels on them. A bit like below? .. Both the row and column labels really are of no use to use. They look really bad don’t you think?.

Creating A Pivot Table.

Here is a recap of how to create a quick Pivot Table.

Select the data set you want to use for your table
The first thing to do is put your cursor somewhere in your data list
Select the Insert Tab
Hit Pivot Table icon
Next select Pivot Table option
Select a table or range option
Select to put your Table on a New Worksheet or on the current one, for this tutorial select the first option

Click Ok

The Options and Design Tab will appear under the Pivot Table Tool

Select the check boxes next to the fields you want to use to add them to the Pivot Table

Quick Pivot Table Hack For Great Column And Row Names

Do you manually rename the column and row names?. You do ?. No need my friend, a couple of mouse clicks will insert the better row and column labels for you. How cool is that?

Select anywhere in your table

Pivot table Tools | Design

Layout Group | Report Layout

Show In Tabular Form
Ta Dah! The correct row and column labels are inserted automatically for you

Want To Know More About Pivot Tables?.

If you want some more information on Pivot Tables then check out my blog posts below.

Why You NEED To Know About Pivot Tables

MS Excel Tutorial- What Is A Pivot Table? And How To Create Your First One.

You can also check out my YouTube channel for some even more regular Excel tips.

If you want even more training then I recommend the following awesome resources below from My Online Training Hub

So, if you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. It’s free, who doesn’t like free stuff?.

Excel Tip – How To Count Unique Items In Excel Pivot Table

June 27, 2016 by Barbara

If you using Excel versions prior to 2013 then you may want to look away now…only kidding!!!! But this tip really does only apply to Excel 2013 onwards, that is the ability to count unique items in your Pivot Table.

First let’s use a data table in Excel as our data source.

Select your data set (CTRL+A)
Hit CTRL+T
Confirm your data table has headers

Create your Pivot Table in the usual way by selecting the data table (or the shortcut keys ALT+N+V)

Insert Tab – Tables Group – Pivot Table

To ensure you can use the Distinct Count function this is where where MUST tick Add This Data To The Data Model in the Create Pivot Table Dialog Box

(the data model is part of Power Pivot and works even if you dont have it installed)

Simply drop the field you want to make a unique count of into the Values area in this case it is Product Names

Then navigate to the Value Field Settings of the value you added

At the bottom of the list you will now see Distinct Count (this does not appear if you do not click Add To Data Model)

In this example I have chosen to count the number of unique products in the product list. The result is 4 unique items. Easy as that. This is a handy feature that has at last been included in the Pivot Table functionality, saving a lot of time and effort removing the need for extra helper columns and formulas in your data to accommodate a unique or distinct count feature.

Note this is not available in Excel versions prior to Excel 2013.

Want to watch the Video?

Excel Tip: How To Use Distinct or Unique Count In An Excel Pivot Table

Hide all of your Excel formulas from prying eyes!

Undertand and debug Excel formula errors

Formula Friday – Create A Dynamic Pivot Table Data Source Using OFFSET and COUNTA Functions

March 18, 2016 by Barbara

Happy Friday Excellers…..

Pivot Tables are great for taking hundreds or even thousands of rows of data and be able to summarise and analyse your data set with a few clicks of your mouse, if you want to read more about creating your first Pivot Table the you can check out my blog post here or my YouTube Video here.

Using A Dynamic Data Source

So, if you continually add extra rows to your Pivot Table data source, or indeed remove them it would be really useful to have Excel automatically adjust the data source to accommodate these changes. There are a few ways to create a dynamic data source, but today let’s look at using a dynamic formula to define a Named Range as the data source. Let’s go ahead and create it.

The Syntax of OFFSET Formula is-

reference – this argument is required it is the reference from where you want to base the OFFSET. This must refer to a cell or a range of adjacent cells.

rows – this argument is required it is the number of rows either up or down that you want the left uppermost cell to refer to.

columns – this argument is required it is the number of columns, left or right that you want the upeermost cell to refer to.

height – this argument is optional and is the height in the number of rows that you want the returned reference to be – it must be a positive number.

width – this argument is optional and is the width in the number of columns that you want the returned reference to be -it must be a positive number.

This example uses Excel 2013.

Formulas Tab – Defined Names Group- Define Name

In the New Name dialog box, call the named range something, I have called this range “my_data_range”.

In the Refers To section is where we enter the OFFSET Formula, which returns a reference to a range specified by number of rows and columns.

I have my sample data set below which is four columns in width- so I need to design a dynamic range which is four columns wide.

offset1

The arguments in this OFFSET formula are-

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

The data is contained Sheet1

Reference Cell -Sheet1!$A$1 – as this is a Pivot Table, we need the Column Names so we start at reference A1

Rows to offset -0

Columns to offset – 0
Number of rows –COUNTA(Sheet1!$A:$A)
Number of columns -4

The COUNTA part of the formula counts the number of values in the range that are not empty. We use this in the ROWS argument. When you add a value to the range i.e a new row of data, COUNTA($A:$A) increases. As a result, the named range expands. (Or reduces as rows are deleted) Cool huh?!.

offset2

There we have a dynamic range for our Pivot Table.

So, we need to do now is use the named range “my_data_range” as the data source for our Pivot Table.

offset3

You can download the example workbook to work through the OFFSET formula here.

[ddownload id=”9289″]

More Excel Tips

Excel Pivot Table Tip – Show Only The Top 10 Items In A Pivot Table

December 7, 2015 by Barbara

This tip is really useful if you have a Pivot Table with a large number of Row Items such a product ID’s and you only want to show your top selling products. You may have hundreds or even thousands of products but you only want to look at the data of your Top 10 selling products.

Instead of manually choosing the top 10 items… with a few clicks Excel does all of the hard work.

First we start with a standard Pivot Table- if you need more information on Pivot Table Basics you cam check out my Pivot Table Week posts right here.

Once your Pivot is created select the arrow to the right of the Row Header – in my example it is Product.

top 20 pivot table

Click Value Filters
Then Top 10
In the Top 10 Dialog Box you can fine tune the filter, but in this case I want the default of 10 items based on Total
To finish hit Ok

top 10 pivot table

Now your Excel Pivot Table will only display the top 10 produts based on their totals. This saves time sorting the Totals then manually clicking the top 10.

top 10 pivot table.1

More Excel Tips

[amazonjs asin=”0789748754″ locale=”US” tmpl=”Small” title=”Excel 2013 Pivot Table Data Crunching (MrExcel Library)”]

[amazonjs asin=”1932802444″ locale=”US” tmpl=”Small” title=”Learn Excel 2007 through Excel 2010 From MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis and Much More – 512 Excel Mysteries Solved”]

[amazonjs asin=”1118842243″ locale=”US” tmpl=”Small” title=”Excel Dashboards and Reports For Dummies”]

Excel VBA Tip -How To Reset All Pivot Table Slicers At The Click Of A Button

November 30, 2015 by Barbara

I have recently been working on a spreadsheet solution based very much on Pivot Tables and a good deal of Slicers to enable an easy of drilll down into the data. If you have been using any version of Excel from Microsoft Excel 2010 onwards, then you probably have already used slicers to navigate around the dat in your Pivot Tables.

Slicers provide buttons that you can click to filter PivotTable data, but in addition to the quick filtering, slicers also indicate the current filtering selection, which makes it easy to understand what exactly is shown in a filtered Pivot Table report. In this example below which is an extract of the number of data reccords at each sales stage in my prospecting database you can easily see that the data is filtered on the appointment data.

So this drill down seemed to be all well and good, with my spreadsheet solution, but one issue that did come up was the number of clicks it took to ‘Start Over’ or reset all of the Slicers back to show all of the data. (There were quite a few slicers on the worksheet- like 4). Whether this is deemed a lot is probably a matter of personal opinion….. and how versed you are in manipulating the worksheet using the Pivot Table Slicers but the consus from this audience was that there were too many clicking options.

The challenge was that the slicers were needed to be there to drill down but took too long to reset.

So, I decided to use a teeny piece of code to enable one button to reset all of the Slicers with one click. So here is the small piece of code for my macro which I then attached to a button on the worksheet.

Sub ClearMySlicers()
Dim Slcr As SlicerCache
For Each Slcr In ActiveWorkbook.SlicerCaches
Slcr.ClearManualFilter
Next
End Sub

Open up your Excel worksheet and hit F11 to open the Visual Basic Editor. (if you need to enable the Developer Tab you can find out more here)
In the left hand drop down box double click on the workbook and paste the code in the window. (This will create a macro that runs when we select it. This macro will be called ClearMySlicers).

x out of the delveoper window and we can check on the macro
Developer Tab – Code Group – Macros to bring up the Macro Dialog box.

We could hit Run from here everytime we want to reset the Pivot Slicers, but it is more simple to just provide the workbook users with a button to click.