What Is Pivot Table Used For In Excel

You tin can also join our FREE Excel Pivot Table webinar training where I will teach you the MUST KNOW Excel Pin Table tips & tricks that will make you an Excel analytical PRO within ane HOUR….

*** Sentinel our video and step by step guide beneath with costless downloadable Excel workbook to exercise ***

i. Tables

Excel Tables are very powerful and have many advantages when using them.  Yous should start using them asap regardless of the size of your data set, every bit their benefits are HUUUGE:

1. Structured referencing;
2. Many different born Tabular array Styleswith colour formatting;
three. Use of a Total Row which uses built-in functions to calculate the contents of a item cavalcade;
iv. Dropdown lists that allow you to Sort & Filter;
5. When you scroll down from the Table, its Headers supercede the Column Messages in the worksheet;
6. Remove Duplicate Rows automatically;
7. Summarize the Table with a Pivot Table;
8. Supports calculated Columns so you lot can create dynamic formulas outside the Table;

Footstep i:Select a cell in your tabular array

STEP ii:Let us insert our tabular array! To do that pressCtrl + Tor go to Insert > Table:

STEP three:ClickOK.

Your cool tabular array is now prepare!

2. Inserting a Pin Table

Pivot Tables in Excel allow y'all to analyze thousands of rows of data with just a few mouse clicks.  It is the most powerful tool within Excel due to its speed and output and I will show you lot just how like shooting fish in a barrel it is to create one.

If you are using a tabular array or information set to analyze your information, then yous should always employ a Pin Tabular array which will raise your analytical capabilities too as save y'all heaps of time off your daily routine.

They are used by Project Managers, Finance Analysts, Auditors, Price Controllers, Sales Analysts, Financial Controllers, Human Resources, Doctors, and Statisticians just to name a few.  Heck, I even created an in-depth online course on Pivot Tables , that'southward how in demand this Excel tool is in correct at this moment!

DOWNLOAD EXCEL WORKBOOK

Now that you are familiar with What is a Pivot Tabular array? Let'south sympathise how to insert 1.

Footstep ane:Click in your dataset.

Step ii:Go to Insert > Pivot Tabular array

STEP 3:Place the Pivot Tabular array in aNew or Existing Worksheet

STEP four:Drag and Drop the fields

You now have your Table ready!

iii. Drill downward to audit

When yous are using a Pivot Tabular array in Excel and desire to know what data makes up a certain value, all you have to do is double click on that cell.

This volition open up a brand new Sheet with all the rows of data that make up that value.

NB. This is an extraction of your data source, so if you edit the information and Refresh your Pin Table and then cypher will happen.  Any changes demand to be made in your main data source.

If y'all want to get rid of this sample data, all you have to practise is press CTRL+Z and press DELETE in the popup box.

So go ahead and double click on any values (including SubTotals and GrandTotals) inside your Table to view the information that makes upward your selected value.

STEP one:Double click on any value prison cell within the Pin Table

This opens up a new sheet with the data that makes up the selected cell.

Note: You lot tin can modify the information that is in this new sheet but that volition not affect the Tabular array or your original data source.

4. Refresh

When the information in your data set gets updated you need to Refresh your Pin Tabular array in Excel to see those changes in your Pivot Table.  At that place are three ways to practise this.  Starting time click on your Table and:

1. From the Ribbon choose: PivotTable Tools > Options > Refresh

ii. Press ALT+F5

3. Correct-click in your Table and choose Refresh (see this option below)

STEP 1:Change the data in your data set.

STEP two:Click on the Pivot tabular array.

Footstep iii:Right-click and select Refresh.

Your values in the table are now updated!

5. Subtotals

When you lot create a Pivot Table in Excel that has multiple fields in the Row Labels, Excel volition automatically add a Subtotal to the top of the Group.

Understanding What is a Pivot Table is the kickoff stride? What about if you desire to change the Subtotals to prove at the bottom of the Group or take the Subtotals out altogether?

Well, you take that flexibility when yous are dealing with Subtotals, here is how:

Pace 1:  Enter at to the lowest degreeii Fields in the Row Labels

STEP 2:  Click in your Pin Table and go to PivotTable Tools > Pattern > Subtotals

STEP 3:  You tincull either of the three options:

At present that you know what is a Pivot Table, let'southward go fifty-fifty more proficient in this.

6. Report Layouts

Pivot Tables have three different layouts that you lot can choose from: Compact, Outline, and Tabular Course.

You can choose from each layout past clicking in the Table and going to PivotTable Tools > Design > Report Layouts

They each take their advantages and disadvantages and I will show you what each one of them provide beneath:

COMPACT LAYOUT (New in Excel 2010)

Advantages: Optimizes for readability; Keeps related data in one cavalcade

Disadvantages: If yous copy and paste the data into a new worksheet information technology will be harder to exercise farther assay

OUTLINE LAYOUT

Advantages: Includes Field headers in each cavalcade; Can Repeat All Particular Labels; Can reuse the data of the Pivot Table to a new location for further analysis; Classic Pin Tabular array manner

Disadvantages: Takes besides much horizontal infinite

TABULAR LAYOUT

Advantages: Includes Field headers in each column; Can Repeat All Item Labels; Meet all data in a traditional tabular array format used in Tables since their invention; Tin reuse the data of the Pivot Table to a new location for further assay

Disadvantages: Takes likewise much horizontal space; Subtotals tin can never announced at the elevation of the grouping

what

7. Change Count of to Sum of

The no1 complaint that I become is"Why do my values show as a Count of rather than a Sumof ?"

Well, there are three reasons why this is the case.

1. In that location are blank cells in your values cavalcade within your data set; or

2. There are "text" cells in your values column inside your data set; or

3. AValues field is Grouped within your Table.

1. BLANK Prison cell(S):

So if you have at least one blank cell in aValues column, Excel automatically thinks that the whole column is text-based.  Pretty stupid only that'southward the way it thinks.

    2. TEXT Jail cell(S):

Also if you have a prison cell that is formatted equallyTextwithin yourValues cavalcade, then it will as well cause information technology toCount rather thanSum.  This normally happens when you lot download data from your ERP or external system and it throws in numbers that are formatted as text eastward.g. 382821P

We get the annoying Count of Sales beneath:

Have a expect at the following tutorials that show you lot how to locate blank cells.

Find Bare Cells In Excel With A Color

EXCEL Prepare:

Step one: Yous will demand toenter a value or a zippo within this bare or text formatted cell(s)

Footstep ii:Get over to your Pivot Table, click on theCount of….  anddrag it out of theValues area

STEP iii: Refresh your Pivot Tabular array

STEP 4: Drop in theValuesfield (SALES) in theValues area once again

    iii. GROUPED VALUES:

Let's say that you lot put aValues field (e.g. Sales) in the Row/Column Labels and so you lot Group it.

When yous drop in the sameValues field in theValues area, you volition also become a Count of…

EXCEL FIX:

Pace 1:  Right Click on the Grouped values in the Pivot Tabular array and chooseUngroup:

STEP two: Drag theCount of SALES out of the Values area and let go to remove it

Step iii: Driblet in the SALES field in the Values surface area one time once again

It will now show aSum of SALES!

Due north.B. Sometimes you will demand to locate the Table that has the Grouped values. The SALES field may not be evident that it is Grouped, particularly if information technology is not selected in the Row/Cavalcade labels.

You may need to elevate and drop this field from thePivotTable Fields and into the Row/Column Labels expanse to confirm that it is Grouped.

8. Number formatting

Yous tin easily format the values simply by Right-Clicking on a value and choosingNumber Format.  Then you lot can choose from the many dissimilar formats, likeNumber, Currency, Percent, or Custom.

Footstep 1:Right-click in the Pin Tabular array and choose Number Format

Stride ii:Choose your desired format

The Pivot table is now updated with your number formatting!

nine. Format error values

Whenever you lot practice a adding in an Excel Pin Table you may go an error value like a #DIV/0!

This looks ugly when you are presenting important data.  Luckily you can override this with a custom value or text.

To activate this you need toRight Click in whatsoever Value in your Excel Pin Table and choosePivotTable Options and "Bank check" the box that says:For mistake values bear witness

This activates the box and yous can now enter any value or text that you lot want to show whenever your adding has an mistake.

STEP 1:We take an mistake in the calculation.

STEP 2:Right-click on any value and Go to Pivot Tabular array Options.

STEP 3:Check the Box: For Fault Values Testify

Stride 4:Enter whatsoevertext or value

At present your error values are properly formatted!

ten. Format empty cells

I am sure that you have come up beyond a Pivot Table which has empty jail cell values and thought"What the hell is happening here?"

This is because your data source has blank cells for sure items, which happens from time to time.

This can be fixed in your Table and you can enter a value or text in place of that horrible looking and solitary bare cell.

You need to click in yourPivot Tabular array > PivotTable Tools > Options > Options > Layout & Format > Format > For empty cells evidence: enter a value or text in this box.

Read the tutorial beneath to meet how this is achieved…

DOWNLOAD EXCEL WORKBOOK

Stride one:Our pin value for North is blank, let united states of america change this!

Step 2:Go to Pivot Table Tools > Options > Options

STEP 3:Ready For empty cells show with your preferred value

All of yourblank values are now replaced!

11. Keep column widths upon refresh

Each time y'all Refresh a Pin Table you will almost likely go bellyaching at the fact that the cavalcade widths that you worked so hard to align – will return back to normal 🙁

Exercise not fearfulness, Pin Tabular array Options is here!

All you demand to do is Correct Click in the Tabular array and choose PivotTable Options and so under the Layout & Format tab y'all need to "uncheck" the box that says:Autofit column widths on update

Next time you update your data and Refresh your Table, the column width will never alter 🙂

Step 1:Right-click in the Tabular array and select Pin Tabular array Options

STEP 2:UncheckAutofit Column Widths on Update

STEP 3:Update your information

Stride 4:Refresh your table

Our Pivot Tabular array column widths do not modify anymore!

12. Show report filter on multiple pages

When you are using an Excel Pivot Tabular array you can show the items within the Report Filter on separate sheets inside your workbook.

Say that you have created an awesome Pivot Tabular array which shows total sales and number of transactions per region.

Y'all tin drop in yourCustomer field in the Study Filter and replicate the Pivot Table for each of your customers in a split upSheet.

All you need to exercise is click inside your Table and in the menu ribbon nether PivotTable Toolsouthward choose the Optionstab and and so select the Options drop-down  and choose Show Study Filter Pages.

Each of your customers volition accept their unique Pivot Table in a separateSheet with their individual sales and transactional metrics.

Hither is our pivot table:

STEP 1:Drop theCustomerField in the written report filter.

Pace 2:Get to Options > Options Drib Down > Show Report Filter Pages

Stride 3:PrintingOK.

Each customer's table will show in a unique sail!

13. Average

A Pin Table is the almost powerful feature within Excel as information technology allows you to analyze your data in many different means, all with a press of a button.

The Summarize Values By  option allows y'all to choose a type of calculation (Sum, Count, Average, Max, Min, Count Numbers Product, StdDev, StdDevp, Var, Varp) to summarize information from the selected field.

As a default when you driblet in avalues field in the Values surface area of the Pivot Table information technology willSumit for you and give yo aSum of Values.

Yous can change this calculation to anBoilerplate very easily, which will show you lot the Average values for your data.

STEP one:  Click in your data and go to Insert > Pivot Tabular array

Pace two:This volition bring upwards theCreate Pin Table dialogue box and it volition automatically select your information`s range or table.

In the Choose where yous desire the PivotTable report to exist placed,  yous tin can either cull a New Worksheetor an Existing Worksheet .

If you choose a New Worksheet  information technology will place the Pivot Table in a brand new worksheet (e.g. Sheet2).

If you decide to put the Pin Table in an Existing Worksheet,  you lot volition need to select the location by pressing thecrimson arrow,choosing the cell where you want your Pivot Table to be placed, and then pressing theENTER key twice to confirm.

STEP 3:  Y'all will at present need todrag and drop the Fields in the different areas of your Pivot Tabular array

Pace 4:Now that your Pivot Table is set, y'all need toRight Click in whatever of the Table values and choose Summarize Values By > Average

STEP 5:  Now you have your Pin Table written report showing the Average Sales values per Region for each year:

14. Show a unique count

Excel 2013 added some new features to its arsenal and one that has been well overdue was the singled-out or unique count.

Previously when we created a Pivot Tabular array and dropped acustomer field in the Row Labels then again in the Values area nosotros got the "total number of transactions"for each customer.

Merely what well-nigh if nosotros want to show the full unique customers?

In Excel 2013 we tin can, by using the newly created Pin TableData Model:

STEP 1:  Click in your data source and go to  Insert > Pivot Table

Step 2:  The important step here is to "bank check"theAdd together this to the Data Modelbox and pressOK

STEP 3:  This volition create a Pivot Table.  At presentdriblet theCustomers field in the Row and Values areas which will requite you lot the "total transactions" for each customer

Pace four:  To go a Singled-out Count, you need to click on the Values drop-down for theCount of Customers and select the Value Field Settings

STEP 5:  UnderSummarize Values By tab, select the last pick,Distinct Count and printingOK

15. % of Grand Total

Excel Pivot Tables accept a lot of useful calculations nether the Prove VALUES AS choice and 1 that can assist y'all a lot is the PERCENT OF GRAND TOTAL calculation.

This selection volition immediately calculate the percentages for you from a table filled with numbers such every bit sales data, expenses, attendance, or anything that can exist quantified.

In the example beneath I show y'all how to get thePercent of Grand Full:

STEP i:Insert a new Pivot table by clicking on your data and going to  Insert > Pivot Table > New Worksheet or Existing Worksheet

STEP 2:  In theROWS section put in theSales Month field, in theCOLUMNS put in theFinancial Year field and in theVALUESarea you need to put in theSales field twice, I explain why beneath:

Footstep iii:Click the 2d Sales field's(Sum of SALES2) drop downwardly and choose Value Field Settings

Footstep iv: Select theEvidence Values As tab and from the drop down choose % of Grand Total.

As well alter theCustom Proper name intoPercent of Chiliad Full to make it more than presentable. ClickOK.

Footstep 5:  Find that the  Percent of Grand Totaldata is in a decimal format that is hard to read:

To format the Percent of Grand Total column, click the 2d Sales field's(Percentage of Thou Full) drop down and choose Value Field Settings.

The goal here is for us to transform numbers from a decimal format (i.e. 0.23), into a per centum format that is more readable (i.e. 23%).

STEP 6: Click theNumber Format button.

Footstep vii: Inside theFormat Prison cellsouthward dialog box, make your formatting changes within here and pressOK twice.

In this example, we used thePercentagecategory to make ourPercent of M Total numbers get more than readable.

You now have your Tabular array, showing the Percentage of Thousand Totalfor the sales information of the years 2012, 2013, and 2014.

All of the sales numbers are now represented as a Percent of the Thousand Total of $32,064,332.00, which you can come across on the lower correct corner is represented as100% in totality:

16. % of Column Total

Excel Pin Tables accept a lot of useful calculations under theProve VALUES AS pick and one that tin help y'all a lot is thePercentage OF Column Total calculation.

This option volition immediately calculate the percentages for yous from a table filled with numbers such as sales data, expenses, attendance, or anything that can exist quantified.

In the example below I show you how to get thePercent of Column Total:

Stride i:Insert a new Pivot table by clicking on your data and going to  Insert > Pin Table > New Worksheet or Existing Worksheet

Footstep 2:  In theROWS department put in theSales Month field, in theCOLUMNS put in theFiscal Year field and in theVALUESexpanse you need to put in theSales field twice, I explain why below:

STEP iii:Click the second Sales field's(Sum of SALES2) driblet downwards and choose Value Field Settings

STEP iv: Select theProve Values Every bit tab and from the drop-down choose % of Column Total.

Also, alter the Custom Name intoPercent of Column Full to arrive more presentable. ClickOK.

STEP 5:  Notice that the  Pct of Cavalcade Fulldata is in a decimal format that is hard to read:

To format the Percent of Column Total column, click the 2nd Sales field'due south(Percent of Cavalcade Total) drop downwardly and choose Value Field Settings.

The goal hither is for us to transform numbers from a decimal format (i.e. 0.23), into a percentage format that is more readable (i.e. 23%).

STEP half-dozen: Click theNumber Format button.

Stride 7: Inside theFormat Cells dialog box, make your formatting changes within hither and pressOK twice.

In this instance, we used thePercentagecategory to brand ourPercent of Column Total numbers become more than readable.

You lot now have your Pivot Table, showing thePercent of Cavalcade Totalfor the sales data of years 2012, 2013, and 2014.

All of the sales numbers are now represented as aPercentage of each column (Years 2012, 2013 and 2014), which y'all can see on each column is represented as100% in totality:

17. % of Row Total

Excel Pivot Tables have a lot of useful calculations nether theSHOW VALUES AS option and one that can help you a lot is thePct OF ROW Total calculation.

This choice volition immediately calculate the percentages for y'all from a table filled with numbers such equally sales data, expenses, attendance, or anything that tin can be quantified.

In the example below I bear witness you how to get thePer centum of Row Full:

STEP 1:Insert a new Pin table past clicking on your data and going to  Insert > Pivot Table > New Worksheet or Existing Worksheet

STEP 2:  In theROWS section put in theSales Personfield, in theCOLUMNS put in theFinancial Year field and in theVALUESarea you need to put in theSales field twice, I explain why below:

STEP 3:Click the second Sales field'due south(Sum of SALES2) drop down and choose Value Field Settings

Footstep four: Select theBear witness Values As tab and from the driblet-downwardly choose % of Row Total.

Also, alter the Custom Proper noun intoPercentage of Row Total to make it more presentable. ClickOK.

Pace 5:  Discover that the  Percent of Row Totaldata is in a decimal format that is hard to read:

To format the Percent of Row Total column, click the second Sales field'southward(Percent of Row Total) drop down and choose Value Field Settings.

The goal hither is for united states to transform numbers from a decimal format (i.east. 0.23), into a percent format that is more readable (i.eastward. 23%).

STEP 6: Click theNumber Format button.

STEP vii: Inside theFormat Cellsouth dialog box, make your formatting changes within here and pressOK twice.

In this case, we used thePercentagecategory to make ourPercent of Row Total numbers go more readable.

Yous now have your Table, showing the Percent of Row Totalfor the sales information of years 2012, 2013, and 2014.

All of the sales numbers are now represented as aPercentage of each row (Years 2012, 2013, and 2014), which y'all can see on each row is represented as100% in totality.

Specially the yellow highlighted ones would total to 100% for the offset row:

18. Difference From

Excel Pin Tables have heaps of calculations under the SHOW VALUES AS pick and one that gets the most use is the Deviation FROM adding.

You can show the values equally theDeparture From previous months, years, 24-hour interval etc.  This is simply smashing when your boss asks yous how you are tracking to the previousmonths, years, days…

In the example below I evidence you lot how to evidence theDivergence From the previous YEAR:

STEP 1: Insert a Pivot Table by clicking on your data and going to  Insert > Pin Tabular array > New Worksheet or Existing Worksheet

Step 2:  In theROWS you have to put theMonths field, in theCOLUMNS theYears field and in theVALUESarea theSales field twice, I explicate why below:

STEP 3:  Now click on the 2d Sales field's(Sum of SALES2) drop down and cull Value Field Settings

Stride 4:Now y'all need to select theEvidence Values Equally tab and from the drop-down cull the Difference From

Step v:You need to select the Base of operations Item: (previous) andBase Field: Fiscal Yr and pressOK.  So it volition read the "Difference from the previous Financial Year"

STEP vi: To format the values you demand to select the Table and go to Pivot Table Tools > Clarify/Options > Select > Entire Pivot Table.

And so yous demand to once once more get to Pivot Table Tools > Analyze/Options > Select but this time select the Values

Now pressCTRL+i to bring upwards theFormat Cells dialogue box and make your formatting changes within here and pressOK.

NB: This will fix the number format permanently and any new field that gets added into the Pivot Table volition have this format.  Coll hey!

STEP vii:  To change the Sum of SALES2  proper noun within the Pivot Tabular array, yous need toclick on a jail cell in the Pin Table which contains theSum of SALES2 and manually make the change and pressEnter

STEP viii:  You need toselect the whole column that contains the empty values andRight Click and selectHide

You now have your Table, all formatted and showing the Difference from the previous Yr:

19. Running Total in

A Running Total In is the accumulation of values over a certain period, similar days, months, or years.  It is sometimes referred to as the Year to Appointment assay.

A Running Total In takes one menstruum's values, and so adds a 2nd period, so a 3rd period…and and so on, to give the accumulated values up to a certain flow.

This tin be shown in dollar form or in per centum form.

With an Excel Pin Table, we can easily include this adding without the need to use any formulas.  Here is how:

STEP ane:  Insert a Pivot Table by clicking in your data and going to Insert > Pivot Tableand choose to insert it in aNew or Existing Worksheet

Footstep two:  Y'all will need to put theMonths Field in the ROW expanse, theYear Field in the Column expanse so put theSales Field in the VALUES surface areatwo times!

STEP three:Right Click on a Totals cell and cullRemove One thousand Full

Step 4:  Right Click on aSum of SALES2 value and select Prove Values Every bit > % Running Full In  > Base Field: Calendar month

STEP v:  You lot canmanually change the proper name of theSum of SALES2  Field by clicking in the cell's title within the Pivot Table:

Footstep 6:Nosotros can insert a Pivot Chart by clicking in our Pin Table and going to PivotTable Tools > Analyze/Options > Pivot Chart

STEP 7:From the Insert Nautical chart dialogue box you demand tochoose the Combo chart and "check"the Secondary Axis box for the YTD% Serial and press OK

Yous now take your % Running Full In values on a Pivot Table and besides shown graphically on a Pivot Chart:

20. Grouping by Date

Grouping Dates is very easy with a Pivot Tabular array.  All you have to do is Right Click on your Date values (which are either in the Row or Cavalcade Labels of your Tabular array), and then choose theGroup choice.  From the dialogue box, you lot tin can choose to Group byDays, Months, Quarters, or Years.

Step 1:Correct-click on your Engagement values and choose Group.

STEP two:Select the combination of grouping yous prefer. ClickOK.

Your pivot table is now grouped by dates!

21. Group by Quarters & Years

Ever encountered needing a quick report of total sales numbers by each quarter, of each year?

I was faced with this same scenario and looking at my data on paw, I but had sales numbers for each individual day.

Grouping these would accept a ton of effort & complex formulas!

Thankfully there is the Pivot Table way, which is quick, like shooting fish in a barrel, and reduces the risks of making any errors….and it makes updating the report hands with whatever new additional data!

In the example beneath I show you how to become the Sales Grouped by Quarters and Years:

Step 1:Insert a new Pivot table by clicking on your data and going to  Insert > Pivot Table > New Worksheet or Existing Worksheet

STEP 2:  In theROWS section put in the Order Engagementfield.

Find that in Excel 2016 (the version that I am using) information technology willautomatically Group the Guild Dateinto Years & Quarters:

Footstep 3:If you practice not have Excel 2016, r ight click on any Row value in your Pin Tabular array and select Grouping

STEP iv: In the Grouping dialogue box,Excelwas able to determine our date range (minimum date and maximum date).

Make sure onlyQuartersand Yearsare selected (which will be highlighted in blue).

This volition group our dates by the Quarters and Years.  ClickOK.

Notice that a Yearsfieldhas been automatically added into our PivotTable Fields List.  This is cool, as we tin can utilize this field for further Pivot Table assay:

STEP five:  In theVALUESarea put in the Salesfield. This will get the total of the Sales for each Quarter-Yr date range:

Now we accept our sales numbers grouped by Years & Quarters!

Observe that we can improve the formatting:

Step 6: Click theSum of SALES and selectValue Field Settings

STEP vii: SelectNumber Format

STEP 8: SelectCurrency.ClickOK.

Yous now have your total sales for each quarterly menstruation!

22. Sorting by Largest or Smallest

At that place are countless times when I had my Pivot Table all setup, but I was not really happy with how the data was sorted. The absurd thing with Excel Pivot Tables, is information technology allows me to sort virtually anywhere!

Allow usa start off with this Excel Pivot Table showing the Sum of SALES for each Year and Quarter.  Make sure to download the Excel Workbook below so that we will have the same starting bespeak.

In the case below I testify yous how to Sort a Pivot Tabular array by Largest or Smallest:

Step 1:Right-click on a Year cell within the Pivot Table.

Get to  Sort > Sort Newest to Oldest

This will sort our Years by descending order.  Notice that 2014 was sorted first.

STEP ii:Now let us try to sort byQuarters.

Correct click whatsoever Quarter proper noun within the Pin Table.

Go to  Sort > Sort Newest to Oldest

This will sort our Quarters by descending order.  Notice that Quarter four was sorted first.

STEP 3:Now let us endeavour for theSum of SALES.

Right-click on a sum value within the Pivot Tabular array.

Get to  Sort > Sort Largest to Smallest

This will sort our sum of Sales past descending club, regardless of the Quarter.

It's pretty absurd on how Pivot Tables Excel allow yous to sort on whatsoever area that you desire to!

23. Sort using a Custom List

ACustom Listing in Excel is very handy to fill a range of cells with your ain personal list. It could be a list of your squad members at work, countries, regions, phone numbers or customers.

The master goal of a custom listing is to removerepetitive work and manual errorsin inputting.

To demonstrate the power of Excel's Custom Lists, we'll explore what's currently in Excel'southward memory every bit a default list:

Stride i: TypeFebruary in the commencement cell

STEP 2: From that first prison cell, click the lower right corner and elevate it to the next 5 cells to the right

STEP 3: Release and you will run into it get auto-populated to July (The succeeding months afterwards February)

At first, it might seem like magic on how Excel does this!

Allow us go straight into the Options in Excel to view how it's being done, and how you tin can create your own Custom List:

STEP 4: Select  theFile tab

STEP 5: ClickOptions

STEP six: Select theAdvanced option

Footstep seven: Whorl all the way downward and u nder theGeneral section, clickEdit Custom Lists

Here you lot can run into the built-in default Excel lists of the agenda months and the days.

If you click on a Custom List, you will see underListing entries that it is greyed out and you cannot make whatsoever changes.  This indicated that it is a default Excel Custom Listing.

STEP 8:  You lot cancreate & add your own Custom List under the Listing entries  section.

Click on NEW LIST under the Custom Lists area and and somanually enter your list, entering ane entry per line:

After typing the values, clickAdd.

In our screenshot below, we added the values of the Greek alphabet (alpha, beta, gamma, and and then on)

ClickOK once done.

STEP ix: ClickOK again

Pace x: At present let's go back into our Excel workbook to run across our new Custom List in action. Type alphaon a cell

STEP xi:From that cell,click the lower right corner and elevate it to the side by side five cells to the right

STEP 12: Release and you will meet it get auto-populated to zeta, which is based on ourCustom Listing created in Step 8

24. Filter by Dates

There are an array of differentEngagement filters in a Pin Table.  Y'all can filter by a detail date range, for case:by this calendar week, next month, adjacent quarter, next year, last twelvemonth, year to date and the listing goes on and on.  This is useful if you desire to see what invoices are due to exist paid this month or what sales transactions were included in a particular quarter.

Beneath I prove you a few quick Pivot Tabular array filter examples.

Pace one:Go toRow Labelsand select Date Filters > Between

Step 2:Place a date range. ClickOK.

Your pivot tabular array is at present filtered by the dates!

STEP three:Permit us try another one. Go toRow Labelsand select Engagement Filters > Adjacent Quarter

Your pin table is at present filtered by the next quarter!

25. Filter by Values – Top 5 Items

Y'all can easily filter your Pivot Tabular array to testify your Pinnacle X customers.  There are lots of different Value Filters to cull from and one of my favorites is the Top 10 Filter.

Here is our pivot table:

Stride one:Become toRow Labels > Value Filters > Pinnacle 10

STEP ii:Set it to theTop 5 Items. ClickOK.

Your pivot tabular array is now filtered!

26. Insert a Slicer

Slicers in Excel are visual filters or interactive buttons that permit you to see what items have been chosen within a Pin Tabular array.

They are a new feature from Excel 2010 onward and Mac for Excel 2016 and a must for anyone wanting to wow their dominate by adding interactivity in their reports!  I testify you lot how to insert a Pivot Table Slicer below:

STEP 1:Select your Pivot Table by clicking anywhere inside information technology.

Step two:  Become to Options(Excel 2010)/Analyze(Excel 2013&2016) > Insert Slicer

Select theMonthandYearFields.

ClickOK.

Your slicer is at present ready!  Go crazy clicking, slicing and dicing your information!

TIP: Hold down the CTRL fundamental to select multiple items in your Slicer.

27. Slicer Styles

There are several different Slicer Stylesavailable for you when you lot click on an Excel Slicer.

These Slicer Styles  add color to your Excel workbook to gives it some much needed life!

You can also add columns to a Slicer and you lot can also find more Slicer options by Right Clicking  on a Slicer.

I will show y'all how piece of cake it is to choose the unlike Pivot Table Slicer Styles:

STEP one:Select your Slicer.

STEP 2:  Get to Slicer Tools > Options > Slicer Styles

Select a way you adoptby clicking on it with your mouse.

If you do non like the chosen Style, just follow the process in a higher place and choose another color!

Your Pin Table Slicer now has come to life!

28. Slicer Connections for multiple pin tables

Normally when you insert an Excel Slicer information technology is only connected to the Pin Table that you are inserting it from.

What near if you had multiple Pivot Tables from the same data gear up and wanted to connect a Slicer to all of the Pivot Tables, so when you press a button all the Pivot Tables modify?

Well this is possible with the Report Connections (Excel 2013 & 2016) / PivotTable Connections (Excel 2010) choice within the Slicer.  This is how it is accomplished:

Step ane: Create two Pivot Tables by clicking in your data gear up and selecting Insert > Pin Table > New Worksheet/Existing Worksheet

Setup Pivot Table #1:

ROWS: Region

VALUES: Sum of Sales

Setup Pin Tabular array #ii:

ROWS: Customer

VALUES: Sum of Sales

Stride 2: Click in Pivot Tabular array #1 and insert a MONTH Slicer by going to PivotTable Tools > Clarify/Options > Insert Slicer > Month > OK

Step 3: Click in Pivot Table #ii and insert a Yr Slicer by going to PivotTable Tools > Clarify/Options > Insert Slicer > Yr > OK

Stride 4: Right Click on Slicer #1 and go toReport Connections(Excel 2013 & 2016)/PivotTable Connections (Excel 2010) > "check" the PivotTable2 box and pressOK

STEP five: Right Click on Slicer #ii and get toReport Connections(Excel 2013)/PivotTable Connections (Excel 2010) > "bank check" the PivotTable1 box and printingOK

At present as you select each Slicer's items, both Pivot Tables Excel will change!

Have a look at the following epitome and the tutorial beneath that to meet how this is accomplished using Excel 2013:

29. Dissimilar ways to filter a Slicer

Excel Slicers are the best affair since sliced staff of life!

They are a new characteristic from Excel 2010 onward and Mac for Excel 2016 and are visual buttons that shows you what items have been filtered or selected in a Pin Tabular array.

At that place are several ways that you tin can filter an Excel Slicer.  Download our workbook so you lot can practice:

LEFT MOUSE CLICK:

You can select items from the Slicer by using your left mouse push;

Drag LEFT MOUSE BUTTON:

You can select an array of items by clicking the left mouse button and doing a dragging movement downwards/upwards inside the Slicer;

CTRL KEYBOARD:

You lot can select multiple items by holding down the CTRL cardinal on your keyboard and selecting the Slicer items with your left mouse push;

SHIFT KEYBOARD:

Select a Slicer particular, hold downwards the SHIFT primal on your keyboard and so select another Slicer detail.  This will select a range of items!

xxx. Creating a Calculated Field

Pivot Table Calculated Fields allow you lot to practice mathematical calculations with your Field List.  You tin can use whatever of the Excel mathematical equations, similar /*+-%.

The only limitation is that y'all cannot reference any cells.  Pin Tabular array Calculated Fields tin be used to summate per centum increases on Sales, margin calculations, or Price of Appurtenances sold, as I show beneath.

Here is our Pivot Table:

Stride 1:Click on the Pin Tabular array and Go to Options > Fields, Items, & Sets > Calculated Field

Footstep 2:Set theNametoPrice of Goods Sold

STEP 3:Prepare theFormulatoCOSTS/SALES. This is the formula to exist used for our calculated field.

STEP 4:The formatting is all the same not correct. Right click on the new column and selectNumber Format.

STEP 5:SelectPercentageand clickOK.

Your new calculated is now ready in percentage format!

31. Creating a Calculated Item

Pin Table Calculated Items let yous to practise mathematical calculations with your Particular List.  You can use any of the Excel mathematical equations, like /*+-%.

The only limitation is that you cannot reference any cells.  Pivot TableCalculated Items tin be used to calculate changes between each other, like a month´southward or year's value, averages or summation.

To actuate theCalculated Item you lot have to make sure that youclick on the chosen Detail in the Pivot Table, so go to the PivotTable Tools tab in the Ribbon and go toOptions > Fields, Items & Sets > Calculated Item.

STEP ane:Click on the particular that you want to calculate.

STEP 2:Click on the Pin Table and Become to Options > Fields, Items, & Sets > Calculated Item

Step iii:Set the Proper noun toYear on Twelvemonth Variance

STEP 4:Set the Formula to utilize the Years:'2015'-'2014'.ClickOK.

YourCalculated Item is now fix!

32. Insert a Pivot Chart

Pivot Charts in Excel are an extension of a Pivot Table and they show its values in a graphical representation.  So when you filter a Pivot Table, thePivot Nautical chart updates appropriately.

To insert aPivot Nautical chart y'all demand to click in your Pivot Tabular array, go to thePivotTable Tools tab in the Ribbon and choosePivot Nautical chart.  Below I show you how easy this can exist achieved and in a few clicks you can plow tiresome data in to colorful insight!

Hither is our Pivot Table:

Step one:Click on your Pivot Table and go to Options > PivotChart

Pace 2:Select a Chart type and clickOK.

You lot at present have your coolPivot Chart!

33. Pin Nautical chart & Slicers

Now we will accept this concept once footstep farther and insert a Slicer.  The absurd thing nigh this is that the Slicer will command both the Pivot Table and the Pivot Chart.

See how you lot can start creating some awesome interactive analytical reports in simply a couple of steps.

Here nosotros have our Pin Table and Pivot Chart ready:

STEP 1:Click in your Pivot Table and go to Options > Insert Slicer

STEP ii:SelectRegionandYear.ClickOK

You at present have your Slicer and yous can play around with it! It will affect both the Pivot Tabular array and Pivot Chart!

34. Highlight Cell Rules based on values

A great way to highlight values within your data prepare, Excel Table or Pivot Table is to use Conditional Formatting rules.

Formatting cells that contain a specific criteria, for example,greater than X orless than X, is a expert way to visualize your results.

When your criteria references a cell, and so yous can brand this conditional format interactive.  So as yous manually change the referenced cell´southward value, the conditional format gets updated and you can meet the live results, as shown beneath….

STEP ane:Select a cell in your Pivot Table.

Footstep 2:Go to Dwelling house > Conditional Formatting > New Dominion

Stride iii:SetUse Ruleto the tertiary pick: All cells showing "Sum of SALES" values for "Calendar month" and "Year"

STEP four:Select a rule type:Format Only Cells That Contain

Step 5:Edit the Dominion Description. Go to Cell Value > Greater Than > Select The Cell

Stride six:Select the cell format. ClickFormatand select acolor.ClickOK.

Endeavour it out at present! The highlight at present happens dynamically when y'all update the value.

35. Directional Icons

Using a Pin Table y'all can evidence the variance from your previous calendar month'southward sales very hands…

At present that we take the Pin Table report above, we tin can show the variances per month using an upwards/downwards or directional icon suing Conditional Formatting.

Step i:Click on any variance value in the Pivot Tabular array and go to  Home > Conditional Formatting > Icon Sets > Directional

STEP 2:  This will bring upward the Apply Formatting Rule to dialogue box. Cull the 3rd option as this will use the conditional format on all the values except the Subtotals

Your Pin Table will look like this:

STEP 3:  Now we demand to make some edits in the Conditional Formatting Rule in order to get the Icons right.

Go to Home > Conditional Formatting > Manage Rules > Edit Dominion

Step 4:  Inside theEdit Formatting Rule box you need to make the post-obit changes:

Value = 0

Blazon = Number

"Check" the Show Icon Merely boxand printingOK to confirm the changes:

STEP 5:  In the adjacent screen you will get the Conditional Formatting Rules Managing director.  HitApply to meet the changes andOK to confirm them:

Now you have your Pin Tabular array showing the differences from the previous months with directional icons only!!!

36. Data Bars, Colour Scales & Icon Sets

Conditional Formatting has improved in Excel 2010 with the introduction of Information Bars, Color Scales & Icon Sets.

Data Bars: Includes graphic confined in a cell, proportional to the cell's value – Skilful for Financial Analysis

Color Scales: Includes a groundwork colour, proportional to the cell'south value – Good for Heat Maps

Icon Sets: Shows icons in a prison cell. The icons depend on the jail cell's value – Good for Project Direction reports

STEP 1:Select the range that you desire to use the provisional formatting on.

STEP 2:Go to Habitation > Provisional Formatting

Step 3:It's time to have fun! Select aData Bar, Colour Calibration, or Icon Readyand see what happens!

37. Intro to GETPIVOTDATA

What does it do?

A formula that extracts information stored in a Pivot Table

Formula breakdown:

=GETPIVOTDATA( data_field,pivot_table, [field1, item1],[field2,item2],… )

What it means:

=GETPIVOTDATA( return me this value from the Values Area,any cell within the Pivot Table,[and return me the value that pertains to this Field name, and this Field item],… )

---

The GETPIVOTDATA function in Excel returns data stored in a Pivot Table.  So essentially it extracts the Pin Tabular array data to enable a user to create customized reports.

Think of the Pin Table like your data source, and so annihilation you see in the Pivot Tabular array report can be extracted with the GETPIVOTDATA function and put into a cell within your worksheet.

The GETPIVOTDATA function becomes powerful when yous reference cells to create shell reports, which you tin can see from the tutorial below.

NB.Merely the Fields and Items that are included in the Pivot Table study (Row/Cavalcade Labels and Values area) can be used to extract their values.

Step 1:Nosotros need toenter theGETPIVOTDATAfunction:

=GETPIVOTDATA(

STEP 2:  The GETPIVOTDATA arguments:

data_field

What is the value that we want to render?

Blazon in SALES as nosotros desire to render the sales value:

=GETPIVOTDATA ("SALES",

pivot_table

From which pivot table?

Just reference a cell in the pivot tabular array, let's type in $A$1

=GETPIVOTDATA ("SALES", $A$1,

[field1, item1]

What are the fields that would serve as our filtering criteria?

To get our target sales figure, nosotros volition demand: Sales Region, Financial Twelvemonth, and Sales Quarter. To exercise this we volition demand 3 field-item pairs:

=GETPIVOTDATA ("SALES", $A$1, "SALES REGION", $A27, "FINANCIAL YEAR", B$25, "SALES QTR", B$26)

Footstep 3:Practice the aforementioned for the rest of the cells past copying theGETPIVOTDATAformula to the balance of the cells.

At present your new set of information is gear up!

38. Refresh All for Pivot Tables

When the information in your information source gets updated or changed, you demand to Refresh your Pin Tabular array to see those changes in your Pin Table.

How about if you have multiple Pivot Tables from the same data source in the same workbook? Or;

Pivot Tables that are created from two different data sources in the same workbook?

In these scenarios, y'all can merely select Data >Refresh Alland all your Pivot Tables volition be updated automatically!

Pace 1: Change the information in your data gear up.

Step ii: This is our Pivot Table. Take note of the cell that we expect to exist updated:

Become to Data > Refresh All

The Pivot Table values are now updated with merely a click!

39. Move an Excel Pivot Table

You have your Pivot Tabular array ready, merely it's not in the best location. How tin y'all motion this?

In these scenario, you can simply select Motility PivotTableand you lot can motility an Excel Pivot Table hands!

For our example, allow's motion this lovely Pivot Table upwardly!

STEP ane:  Make sure you have selected your Pivot Tabular array. Get to Analyze > Actions > Move PivotTable

Footstep two:  Select the new location where y'all want to move it. In our example, we selected prison cellA4. ClickOK.

Voila! You accept successfully moved your Pivot Table!

xl. Bear witness/Hide Field List of Excel Pivot Tabular array

Yous take your Pivot Table prepare, withal all of a sudden your Field List is not showing upwards!

In these scenarios, you can simply Bear witness / Hide Field List of Excel Pivot Tableand you tin bring back your Field List easily!

For our instance, this is our Pin Table:

STEP 1: To show the field list, correct-click on your Pin Tabular array and select Show Field List.

Your Field List is now showing! Allow united states of america show in the next step how to hibernate this.

Step 2:To hide the field list, right-click on your Pivot Table and select Hide Field List.

YourField Listis now hidden!

How to Prove / Hibernate Field List in Excel

41. Pivot Table Styles

The default Pin Table style in Excel is very bland and boring, stemming from a lack of creativity from the nerds over at Microsoft!

You tin can give some life to your Excel Pin Table by but changing the Pivot Table Mode!

For our example, this is our Pin Table:

Step ane:  Make sure y'all have your Pivot Table selected. Go to PivotTable Tools > Design > PivotTable Styles

STEP two:Aggrandize the styles list and have fun selecting your preferred mode! Yous can come across I selected the orange way below, as today is a hot mean solar day 🙂

You now take your ExcelPin Tablewith your new warm manner!

How to Alter Pivot Table Styles in Excel

42. Sort manually

You have your Pivot Table gear up, all sorted nicely both from a row and column perspective. All the same, you only need that 1 small-scale sorting tweak or 2.

Well, Excel seemingly has a lot of tricks and you tin fifty-fiftysort an Excel Pivot Table manually!

For our case, let's meet this Pin Table below.  It is sorted by years (2012-2014), and months (Jan-Dec).

Only what if we want to move July to the top, and the year 2014 as the offset cavalcade year?

Step 1: To manually sort a row, click on the jail cell you want to motility. Hover over the border of that cell until you see the four arrows:

Left mouse click, concur and drag information technology to the position y'all want (i.east. up to the start row)

We dragged it to the top so it'south now the first row!

STEP 2: To manually sort a column, click on the jail cell y'all want to movement.  Hover over the edge of that prison cell until yous see the four arrows.

Left mouse click, hold and drag it to the position you desire (i.e. all the fashion to the left)

Voila! You have successfully manually sorted your Pivot Table!

EXTRA TIP: You can click inside a cell e.g. January , and offset typing in another month, like August .  This volition as well manually sort your Pivot Table items.

43. Utilize an External Information Source

When creating an Excel Pivot Table,what happens if your data source is in another location?

Would you have to copy your data into the same spreadsheet?

Well, NO!  You lot tin can just use theExternal Data Sources feature in your Pivot Table and Excel will magically import the data for yous!

You lot can import data into your Pivot Table from the following data sources:

• Excel workbook
• Microsoft Access database
• SQL Server
• Analysis Services
• Windows Azure Marketplace
• OData Data Feed

For our example, we will import data using two data sources, an Excel workbook, and an Access file.

Import from some other Excel Workbook:

Import From Microsoft Access and into Excel:

Wondering how this is even possible? Read on!

DOWNLOAD FILES

Import from another Excel Workbook:

STEP 1:   Go to Insert > Tables > PivotTable

STEP 2:  SelectApply an external data source and click Choose Connexion.

STEP three:  Select Browse for More .

Footstep 4:  Select theExcel filewith your information. ClickOpen.

Pace 5:  Select the first selection and click OK.

STEP 6:  Click OK.

STEP 7:  In theVALUES area put in the Salesfield, for theCOLUMNSarea put in the Financial Year field, and for theROWSarea put in the Sales Calendar month field

Your Pivot Tabular array is ready from the Excel information source!

Import From Microsoft Access and into Excel:

STEP 1:  Now permit u.s. endeavor for an Access information source!

Go toData > Get External Data > From Access

STEP 2:  Select theAccess Database Source file in your desktop or company file path.ClickOpen.

Footstep three:  SelectPivotTable Report and click OK.

Footstep iv: In theVALUES area put in the Salesfield, for theCOLUMNSarea put in the Financial Year field, and for theROWSarea put in the Sales Month field

Your Pin Table is ready from the Admission data source!

More Ways to Import External Data into an Excel Pin Table:

You can too use this functionality to get data from other source types:SQL Server, Analysis Services, Windows Azure, and oData Data Feed

How to Use an External Data Source with Excel Pivot Tables

44. Clear and Delete Quondam Items

Have you ever cleared, deleted, or replaced your Pin Table data/items simply they still show within your Pivot Table filters?

What gives??

Well, you can hands clear your Pivot Table'south old items from your Pivot Table's memory or cache.

In our example beneath we have our Pin Table with the Years showing in the Column area (2014, 2012, 2013):

Pace 1:  Below is our data source and we desire to supplant the year 2012 with 2013, effectively simply showing the years 2014 & 2013.

Become to Dwelling house > Find & Select > Supervene upon

Let u.s.supervene upontheyear 2012with theyr 2013.ClickReplace All.

Step ii:Go dorsum to your Pivot Table.Right clickand selectRefresh.

We accept technically deleted the year 2012 records, so they should be gone from our Pivot Table, correct?

Hmm.. Looking good, the year 2012 is now gone from our Pivot Table!

BUT Await!

Clicking on theColumn Labelsdrib-down list, the Year 2012is still there!  Bloody hell!

STEP 3:Let us ready this!  Go back to your Pivot Table >Right-click and selectPivotTable Options.

Footstep 4:  Go to Data > Number of items to retain per field.

SelectNoneand thenOK. This will finish Excel from retaining deleted data!

Stride 5:  Go back to your Pivot Table.Correct-click and selectRefresh.

Click theColumn Labelsdrop-down list, and the Yr 2012is now gone!  Trouble stock-still!

How To Clear & Delete Onetime Pivot Table Items

45. Count VS Sum

The #1 complaint that I get from Pivot Tables is "Why do my values show as a Count of rather than a Sumof ?"

Well, there are three reasons why this is the case:

i. There are bare cells in your values column within your information gear up; or

2. There are "text" cells in your values column within your data prepare; or

iii. A Values field is Grouped within your Pivot Table.

1. BLANK CELL(S):

So if you lot take at least ane bare cell in a Values column, Excel automatically thinks that the whole column is text-based.  Pretty stupid but that's the mode it thinks.

2. TEXT Prison cell(South):

Also if you have a cell that is formatted as Textwithin your Values column, and so information technology will also cause information technology to Count rather than Sum.  This usually happens when you download data from your ERP or external system and information technology throws in numbers that are formatted every bit text e.g. 382821P

Nosotros get the abrasive Count of Sales beneath:

Have a look at the following tutorials that bear witness you how to locate bare cells:Find Blank Cells In Excel With A Color

EXCEL Ready:

Step i: You will need to enter a value or a cipher within this blank or text formatted cell(south)

Step two: Go over to your Pivot Table, click on the Count of….  and drag it out of the Values expanse

Pace 3: Refresh your Pin Tabular array

STEP 4: Drop in the Valuesfield (SALES) in the Values area once again

    3. GROUPED VALUES:

Let's say that you put a Values field (e.g. Sales) in the Row/Column Labels and then you Group it.

When yous drop in the same Values field in the Values area, you will too get a Count of…

EXCEL FIX:

STEP 1: Right Click on the Grouped values in the Pivot Tabular array and cull Ungroup:

STEP 2: Drag the Count of SALES out of the Values expanse and permit get to remove it

Step three: Drop in the SALES field in the Values area once again

Information technology volition now evidence a Sum of SALES!

North.B. Sometimes you will demand to locate the Pivot Table that has the Grouped values.  The SALES field may not be evident that it is Grouped, especially if it is non selected in the Row/Column labels.

You may need to drag and drop this field from the PivotTable Fields and into the Row/Column Labels area to ostend that it is Grouped.

46. Automatically Refresh

Take you lot had challenges with constantly Refreshing a Pivot Table?

People forget that each time your data source gets updated that y'all volition also demand to manually Refresh your Pivot Table in order for it to get updated and testify the changes fabricated.

A lot of people inquire if at that place is a way to automatically Refresh a Pivot Table, which I totally get.  Automation is why we use Excel, right!

Here I bear witness yous a couple of ways that y'all tin can automatically Refresh a Pivot Table.

   ane. REFRESH Pivot Tabular array UPON OPENING:

This is a bang-up feature and one that almost people don't know about.

It allows you to Refresh your Pin Tables as before long as you open upwardly your Excel workbook.

This is great if your Pin Table's information is linked to another workbook that gets updates by your colleagues and y'all only get to run into the Pin Table report.

STEP i: Right Click in your Pin Table and choose Pivot Table Options:

Stride two: Select the Data tab and check the "Refresh data when opening the file" checkbox and OK

Now each morning that you open up your Excel workbook, you tin exist sure that the Pin Tabular array is refreshed!

   2. Automated REFRESH EVERY 10 MINUTES:

If you lot have your data set up linked in an external data source, you can auto-refresh every 10 minutes.

Your data can exist stored in an external data source such as Access, a Website, SQL Server, Azure Marketplace, etc.

Stride 1: If your data is stored externally, you will need to click in your Pivot Tabular array and go to Backdrop (this volition but exist enabled for selection if y'all have an external data source)

Footstep 2: This will open up the Connection Properties and you will need to select the Refresh every checkbox and manually prepare the time & printing OK.

You tin at present sit back and enjoy a cup of java whilst your Pivot Table gets updated every few minutes:)

Automatically Refresh a Pivot Table

47. Frequency Distribution

With Excel Pin Tables you can do a lot of stuff with your data! But did you know that you tin can even create a Frequency Distribution Tabular array?

Let'south have some fun beneath! I'll show you how easy it is to create your ain Frequency Distribution Nautical chart!

We will create a chart based on this tabular array with Sales values:

Footstep 1:  Let united states of america insert a new Pivot Table. Select your information and Go to Insert > Tables > PivotTable

Select Existing Worksheet and option an empty space to identify yourPivot Tabular array.ClickOK.

STEP 2:  Drag SALES into VALUES and ROWS and you lot'll run across your Pivot Tabular array get updated:

Click on Sum of SALES and select Value Field Settings.

SelectCount and clickOK.

Pace 3:  Nosotros are almost there! Right click on your Pivot Table and selectGrouping.

Accept the suggested values. It volition group our values by ranges of 10,000. ClickOK.

Now it's grouped together!

STEP 4:  Go to Analyze > Tools > PivotChart

EnsureAmassed Columnis selected. ClickOK.

Your awesome Frequency Distribution is now set up!

How to Create a Frequency Distribution

48. Slicer Connexion Greyed Out

Sometimes when you create a Pivot Table and want to insert a Slicer you are unable to do this equally the Slicer button is greyed.

Yous try to click on the Slicer push button but nothing happens.

What gives??

There are two things that tin crusade your Slicer connexion to be greyed out!

ONE: Your file format is in an older/incompatible format (e.k. a .xls file extension)

Ii: You can see the text [Compatibility Manner]right beside the proper noun of your excel file:

Allow me show yous rapidly how y'all can resolve this problem in just a few steps!

STEP 1:  Go toFile > Convert

STEP 2: This will convert your Excel file into a more updated version.

ClickOK.

ClickYeahto reload your workbook.

Voila! You can now insert your slicer!

NB: You can also Salvage As your current file as an .XLSX file format.  Then shut this file and open information technology again and you will be able to use the Slicer button over again!

HOW TO ENABLE THE GREYED OUT SLIDER CONNECTION

Determination

In this tutorial, you have learned What is a Pivot Table in Excel and 50 different things you can practise with an Excel Pin Table.

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

*BONUS* FREE EXCEL Pivot TABLE WEBINAR

Source: https://www.myexcelonline.com/blog/50-things-you-can-do-with-excel-pivot-tables/

Posted by: blackstockwhippyraton62.blogspot.com

Share this post