Excel Pivot Tables: Using Slicers to Filter Data

September 30, 2022 by Austin Chia

Are you looking for a way to filter your Excel pivot tables quickly? If so, then you need to learn about slicers! Slicers are a great way to control the data that is displayed in your pivot table.

In this blog post, we will introduce you to pivot table slicers and show you how to use them through a step-by-step tutorial.

So, if you want to learn how to use slicers with pivot tables, keep reading!

What Are Pivot Table Slicers?

Pivot table slicers are a new feature only found in versions from Excel 2010 onward. They allow you to quickly filter pivot table data by clicking on a value in the slicer.

Why Use Pivot Table Slicers?

Pivot table slicers are a great way to filter pivot table data in Excel. Slicers are an alternative to the default filters in Excel.

They are easy to use and they provide a quick way to change the data that is displayed in your pivot table. You’ll be able to mine data for useful business insights.

Another advantage of using slicers is that they can be used to filter multiple pivot tables at the same time. This is because slicers are connected to pivot tables.

Therefore, if you have multiple pivot tables in your workbook, you can use a slicer to filter all of them at the same time. This can save you a lot of time if you need to regularly filter pivot table data.

When Should Pivot Table Slicers Be Used?

Pivot table slicers should be used when you need to quickly filter pivot table data. They are handy if you have multiple pivot tables in your workbook.

They’re also a great way to filter data when creating an Excel dashboard for your Excel project.

With the use of pivot table slicers in Excel, you’ll get to dig deeper into your data and visualize them better through charts.

Although not as powerful as the filters available in other data analysis tools like Tableau, slicers are easy to create and implement in your work!

How Do Pivot Table Slicers Work?

Pivot table slicers work by connecting to pivot tables. When you create a slicer, you need to specify which pivot table it should be connected to. Once a slicer is connected to a pivot table, it can be used to filter the data in that pivot table.

If you have multiple pivot tables in your workbook, you can connect a slicer to all of them. This will allow you to quickly filter the data in all of the pivot tables at the same time.

How To Use Pivot Table Slicers to Filter Data

Now that you know the basics of using pivot table slicers, let’s take a look at how to use them with a pivot table. We will walk you through the process step-by-step so that you can see how it’s done.

To start, select the pivot table with which you want to use the slicer. Then, click on the “Insert” tab and then click on “Slicer.”

In the “Select a slicer” window, select the field that you want to use as a slicer. For this example, we will use the “State” field.

Next, click “OK.” Your pivot table should now have a slicer associated with it. To use the slicer, simply click on the items that you want to include in your pivot table.

For example, if you only want to see data for the states of “CA” only, click on “CA”. The pivot table will update to only include data for that state.

And if you want to filter and include bot, hold Shift and click both “CA” and “NY”. The pivot table will update to only include data for those two states.

By using this slicer, you’re able to quickly switch between states by selecting the values you need. This can be great when presenting important data using your pivot table.

Rather than just using the regular filters in the pivot table slicers give you a more intuitive way to interact with your data!

Having this knowledge of using slicers in your pivot tables is an essential skill in data analytics in business, where presentation summaries are used in day-to-day operations.

Key Takeaways:

Pivot table slicers are a great way to quickly filter pivot table data.
They’re easy to use and can be connected to multiple pivot tables.
You can use them to filter data by region, sales, criteria, etc.

Conclusion

As you can see, pivot table slicers are a great way to quickly filter your data. So, if you haven’t already started using them, we encourage you to do so!

Thanks for reading!

Enjoyed this basic tutorial on separating data? Having basic training in Microsoft Excel is important for success in many jobs. If you want to learn more about how to use Excel, check out the other blog posts or sign up for one of our Excel trainings!

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Author Bio

Austin Chia is the Founder of Any Instructor. A data analytics and Excel enthusiast, he seeks to help others learn more about Excel and anything related to analytics and tech. He has experience as a data analyst and data scientist in healthcare and research.

Excel Pivot Table Tips: Refreshing the Table After Source Data Changes

February 1, 2017 by Tyrone Pernsley

The pivot table in Excel is one of the most vital and versatile tools available. It allows you to look at your data from a wide range of customizable views. In the following guide, we explore how to update the Pivot Table after the source data changes.

There are a variety of reasons you might need to update the pivot table. Maybe you get a weekly report that needs to be added each week. Instead of recreating the pivot table, you can simply refresh it. Maybe there were errors in the source data that needed to be corrected. Again, it’s simpler to refresh than to recreate.

Let’s say you had the following spreadsheet:

And you created this pivot table for it:

Then your manager informs you of a price correction on the last 4 items. They went up $50 each. For such a minor correction, it would be a waste of time to create a whole new pivot table. Instead, you will:

Make the source data correction
Go to the tab with the pivot table
Go to the Data tab on the Excel ribbon
Select Refresh

You can also use the keyboard shortcut Alt+F5 to perform this task.

As you can see in the animation above, once you apply the refresh option, the data in the table automatically updates with the source data corrections.

We hope you now feel comfortable making corrections to your pivot table source data and applying the refresh. This is one of many tools available to help you perfect your pivot tables.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Date Calculations Part 2: Setting Sequential Dates

December 21, 2016 by Tyrone Pernsley

Excel offers a variety of ways to perform date calculations. In part 1 of this series, we showed you how to find the difference between dates. In this week’s post, we cover setting sequential dates in Excel.

Sheryl is getting ready to perform her annual budget. Her job pays her every two weeks. As a result, 10 months of the year, she receives two paychecks, but there are two months where she ends up with three paychecks. She wants to find each payday in the upcoming year and highlight the months where she will receive three checks.

She starts off with her current payday date:

To find the two week pay schedule dates she will:

Enter the formula =A2+14
This yields the next pay day
Then copy the formula across to find all dates
Then look for the date with three in a month and highlight them

In the example, we see that Excel automatically finds the date 14 days from the original date input. In copying the formula across it populates each date two weeks apart.

We at Learn Excel Now hope you now feel confident setting sequential dates in Excel. This process can be used for a variety of business and personal situations.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Date Calculations Part 1: Finding the Difference Between Two Dates

December 14, 2016 by Tyrone Pernsley

Excel has several built-in date functions you can use to quickly find important information. These are known as Excel date calculations. Today, we will focus on how to find the difference, in number of days, between two different dates. We will also cover how to find a date based on number of days from a given date.

For today’s example, we will use the following spreadsheet outlining due dates for a library:

Here, we have the due date and the current date. To enter the current date into your spreadsheet, you will use the formula =TODAY(). It will autofill with today’s date.

What we don’t have is the number of days overdue, and the overdue fee. To find that, we will:

Subtract the due date from the current date

Multiply the number of days by the fee per day

As you can see, Excel makes it easy to find the exact number of days and then calculate the amount owed. This process involves first finding the number of days and then separately finding the amount due. But you can also build the date calculation right into the fee calculation.

You will use the parenthesis to indicate which function you want performed first (always remember: Excel operates on the Order of Operations).

So, the first part of the calculation will be: =(B3-A3)

Then, tell the formula to also multiply by the amount owed column: =(B3-A3)*D3

Finding a Date Based on Number of Days

Let’s say you had the same spreadsheet, except that the original due date was missing, but you still have the number of days. Your job is to find the original due date. This is basically a reverse of finding the number of days overdue. You will enter:

=B3-C3

This is how you use Excel to find the number of days between two dates or vice versa. Next week, we will cover additional date functions you will want to know. We hope you found today’s lesson on Excel date calculations useful.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Data Validation: An Introduction

December 7, 2016 by Tyrone Pernsley

Excel data validation allows you to set specific criteria for the type of data that can be entered into a cell or group of cells. In addition to creating that criteria, you can write a brief message on what can be entered into the cell and error message when the wrong information is entered.

In today’s example, we will use the following customer survey as our example:

Here, we need to set data validation so only whole numbers between 1 and 10 can be entered. To do this, we will:

Go to the Data tab
Go to Data Validation
Open the Data Validation Dialogue box
On the Settings tab, set the range (whole numbers between 1 and 10)
On the Input message, enter our unique message
On the Error Alert tab, enter your custom error message

Once you hit okay, the rules will apply. Here is a demonstration in Excel:

As you can see, the only numbers that can be entered are whole numbers between 1 and 10. There are a variety of reasons to use Excel data validation and it depends on the situation and problem you’re trying to solve.

We here at Learn Excel Now hope you found today’s lesson on Excel data validation useful.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Rank Function: What is It & How to Use It

November 30, 2016 by Tyrone Pernsley

One of Excel’s advanced functions is the RANK function. This formula is used to rank numbers in a dataset by either ascending or descending order.

Let’s say you were running a customer loyalty program based on earning points. You need to rank customers’ point totals to determine what rewards they receive. You could do a simple sort, but your boss wants the customer names to stay in the same order.

This is the example we will use:

You will use the RANK function for this challenge, but let’s first explore how the function works.

Syntax of the Rank Function

=RANK(Number, Ref,[Order])

When you use the rank function, your first argument is the number. This is where you specify the cell containing the number you want to be ranked. Next, you identify the range of numbers to be used as the reference (Ref). Finally, you select the order, which is ascending or descending.

Solving the Challenge

So, we will identify B2 as the cell to be used. Then, identify the range as B2 through B11:

Then, select Ascending order and we get our first result:

Before moving onto the remaining lines in the table, we need to add absolute referencing to the range so that it stays consistent while the number being compared changes for each line. A reminder to use the $ between the letter and number of the cell for absolute referencing:

Once you’ve added the absolute reference, you can now copy and paste the formula all the way down to reveal the rank for all lines:

We at Learn Excel Now hope you feel comfortable using the RANK function after this article.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

How to Use the Excel MEDIAN Function

November 9, 2016 by Tyrone Pernsley

In this week’s Excel tip, we cover how to use the Excel MEDIAN function. The MEDIAN function is used the return the median value within the defined range.

For this demonstration, we will use the follow example:

The task assigned for this spreadsheet was to find the median sale amount. To do this, we will use the MEDIAN formula:

=MEDIAN(Number1, [number2]..)

Since the goal is to find the median in a single range, the formula becomes:

=MEDIAN(D2:D21)

And once you enter the formula, you get the return value:

Since there were 20 cells within the range, the formula found the average between two middle values.

We at Learn Excel Now hope you feel comfortable using the MEDIAN function now.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

How to Show Formulas in Excel

October 26, 2016 by Tyrone Pernsley

In this week’s blog post, we cover how to show formulas in Excel. This convenient feature is ideal for identifying which cells contain formulas and what those formulas are.

John was recently hired in human resources for a large sales firm. His job is to manage the salaries for the sales, accounting and shipping departments. Once hired, he received the following spreadsheet from his predecessor.

He was not a fan of the layout and functionality of the spreadsheet. He wanted to change it around to make more sense to him and to give him more control over the data. However, he knew some of the cells contained formulas and moving things around could throw off the whole spreadsheet.

So, his first step in taking ownership over the spreadsheet was to identify which cells are using formulas. There are two ways to show formulas in Excel:

On the Excel Ribbon, go to the Formulas tab and click on the Show Formulas button
Keyboard shortcut: Ctrl+` (Control and the accent mark, aka Tilde)

The following Gif provides a demo using the first option:

However, using the Ctrl+` would result in the same.

We at Learn Excel Now hope you now feel confident finding the formulas in your spreadsheet.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Formulas: Using Trace Dependents to Find Data Solutions

October 19, 2016 by Tyrone Pernsley

In last week’s post, we covered how to use Trace Precedents to find and resolve formula errors. In this week’s follow up, we cover how to use trace dependents to see which formulas use the data contained in a particular cell.

Donna was recently sent her company’s sales spreadsheet:

The commission rate was set to 5% for sales over $500. Her boss wants to increase the rate to 6%. She has two tasks: identify which sales will be affected by the increase and how many sales are over $500.

There are a number of ways Donna can find the data she needs. She could manually count the sales columns. She could use a lookup function. However, she prefers to have a clear visual to show which ones are the important ones.

By using Trace Dependents, she can quickly see which bonuses are for sales over $500, allowing her to determine which ones will be impacted and how many there are. Here is how she used Trace Dependents:

Find the cell with the relevant data – in this case, the 5% in the commission table (Cell I6)
While you are on that cell, click the Formulas tab in the Ribbon
Under the Formula Auditing menu, click on Trace Dependents
The arrows will point to every cell using a formula which uses I6

We at Learn Excel Now hope you feel confident using Trace Dependents now. There are many times and reasons this tool will come in handy.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Formulas: Using Trace Precedents to Find & Resolve Errors

October 12, 2016 by Tyrone Pernsley

The power of Excel comes from the ability to do complicated calculations using formulas and functions. Sometimes, though, there are errors in formulas that give an error message or return the wrong value. In those instances, Excel also provides tools to resolve errors and check your work. One such tool is trace precedents.

The trace precedents tool finds any and all cells that provide information for the target cell. For example, if you summed up the totals in A1 and B1 into cell C1, both cells A1 and B1 would be precedents for C1.

Some errors in Excel are related to one or more precedent cells giving information that throws off the formula. Trace precedents is a great way to determine if there is a cell causing the issue, and if so, which cell needs to be corrected

For an example of using trace precedents to resolve formula errors, we will use the following spreadsheet:

In this instance, the table is using a simple formula to create revenue projections. The previous year’s quarterly total is increased by the projected growth of 5%. As you can see though, the Q3 project, cell I5, is showing an error message.

So, we will use the trace precedents tool to see if there are any cells causing the issue:

In the demo above, the error was created because one of the cells was incorrect. Instead of the cell containing the percentage amount (5% in cell F4) it was trying to pull the definition of that number one cell above (F3).

The issue could have been caused by simple human error. The person entering the formulas could have simply clicked on the wrong cell.

Once the wrong cell was identified using trace precedents, it was a simple matter of editing the formula to the correct cell and the error was resolved.

There are many instances where this tool could come in handy. Aside from resolving errors, sometimes you might just want to see which cells feed into a formula.

Stay tuned for part 2 next week on Trace Dependents!

We here at Learn Excel Now hope you now feel confident using the trace precedents tool to resolve formula issues.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Pivot Tables: Using Slicers to Filter Data

What Are Pivot Table Slicers?

Why Use Pivot Table Slicers?

When Should Pivot Table Slicers Be Used?

How Do Pivot Table Slicers Work?

How To Use Pivot Table Slicers to Filter Data

Key Takeaways:

Conclusion

Author Bio

Excel Pivot Table Tips: Refreshing the Table After Source Data Changes

Excel Date Calculations Part 2: Setting Sequential Dates

Excel Date Calculations Part 1: Finding the Difference Between Two Dates

Excel Data Validation: An Introduction

Excel Rank Function: What is It & How to Use It

How to Use the Excel MEDIAN Function

How to Show Formulas in Excel

Excel Formulas: Using Trace Dependents to Find Data Solutions

Excel Formulas: Using Trace Precedents to Find & Resolve Errors

Connect with Us

Contact

What Are Pivot Table Slicers?

Why Use Pivot Table Slicers?

When Should Pivot Table Slicers Be Used?

How Do Pivot Table Slicers Work?

How To Use Pivot Table Slicers to Filter Data

Key Takeaways:

Conclusion

Author Bio

Connect with Us

Contact

WELCOME BACK!

Reset Password