ExcelInsider Team
When we work with large datasets in Excel, we often need a quick way to filter and analyze information visually. In such cases, using slicers is the most common way. However, usually, we insert a slicer with a pivot table. But every situation does not require this table, and sometimes, we only want to filter the table directly. In such situations, we insert a slicer without a pivot table.
Suppose you are managing a sales tracker that logs transactions daily. Instead of creating a PivotTable every time, you may just want to filter the table quickly by region or product to review sales trends. By adding a slicer to your table, you can click on any region name or product name and instantly see only the relevant rows, making analysis faster and more interactive without extra steps.
In this article, we will learn how to insert slicers in Excel without using a PivotTable by converting the dataset into a table and using the Insert Slicer option.
➤ First, select any cell inside the dataset and press Ctrl + T . Alternatively, you can go to Insert and then select Table.
➤ Now, you will see a pop-up. In it, check the My table has headers box and click OK, and the dataset will be formatted as a table.
➤ Next, click anywhere inside the table, and you will see an option called Table Design at the upper menu bar.
➤ Then, click on the Table Design tab and select Insert Slicer.
➤ A dialog box will pop up with all the column headers ( Date, Product, Region, etc.).
➤ From that box, you can choose any slider field. For our dataset, we want to see the product information according to the region. So, choose the Region. Then, click Ok.
➤ Now, you will see the slicer.
➤ Then, click any region in the slicer. Let’s click on North.
➤ Now, you will see that the table instantly filters our data to show only rows from that region.
➤ Now, let’s try to filter the table for two or more regions at once. Let’s filter it for both the South and North regions. To do it, hold Ctrl and select these two regions at once.
Insert a Slicer in Excel by Converting the Dataset into a Table
The simplest way to add a slicer in Excel without using a PivotTable is by converting our dataset into a Table. It allows us to filter and analyze data interactively with just a few clicks.
We will use the datasets below to explain how you can insert a slicer directly on your dataset without a pivot table and just by turning the dataset into a table.
This dataset contains the revenue of some products that were produced by the same company and sold in different regions.
Step 1: Convert the Dataset into a Table
At first, we need to convert our dataset into a table to insert a slicer directly without a pivot table.
Steps:
➤ First, select any cell inside the dataset and press Ctrl + T . Alternatively, you can go to Insert and then select Table.
➤ Now, you will see a pop-up. In it, check the My table has headers box and click OK, and the dataset will be formatted as a table.
Step 2: Insert the Slicer
Now, our table is ready, and we can insert a slicer to filter our data and make the analysis easier.
➤ Next, click anywhere inside the table, and you will see an option called Table Design at the upper menu bar.
➤ Then, click on the Table Design tab and select Insert Slicer.
➤ A dialog box will pop up with all the column headers ( Date, Product, Region, etc.).
➤ From that box, you can choose any slider field. For our dataset, we want to see the product information according to the region. So, choose the Region. Then, click Ok.
➤ Now, you will see the slicer.
Step 3: Choose the Slicer Field
After we insert the slicer in our table, we can choose the slicer fields to filter for that specific criterion.
➤ Click any region in the slicer. Let’s click on North.
➤ Now, you will see that the table instantly filters our data to show only rows from that region.
➤ Now, let’s try to filter the table for two or more regions at once. Let’s filter it for both the South and North regions. To do it, hold Ctrl and select these two regions at once.
Frequently Asked Questions
Does the Insert Slicer Option Support All Versions of Excel?
No, the Insert Slicer option does not support all versions of Excel. It is available only in Excel 2010 and later for Windows. However, for versions before Excel 2013, you need to use a PivotTable to apply slicers. Also, on Mac, slicers for PivotTables work in Excel 2016 and later. The table slicers are supported only in the newest versions, i.e., Excel 2019 and Microsoft 365.
Can I Use Slicers on Tables Directly in the Excel Online Version?
No, you cannot. Excel Online has limited support for slicers. While you can view and interact with slicers that were created in the desktop version of Excel, you cannot insert new slicers on tables directly in Excel Online. However, you can customize some basic stuff of the slicer, like you can resize it.
How Can I Remove a Slicer from My Dataset?
To remove a slicer from your dataset or worksheet in Excel, first click anywhere on the slicer. It will select the slicer, and then you can simply press the Delete key on your keyboard to remove it. Alternatively, you can right-click on the slicer and choose Remove Slicer or Cut from the context menu.
Wrapping Up
In this article, we have learned how to insert slicers in Excel without using a PivotTable with the help of Insert Slicer. You can do it just by converting your dataset into a Table and applying the built-in Insert Slicer option. It will give you more access to interact with your dataset directly and visualize the data for different criteria easily. Give this method a try, and feel free to reach out if you have any inquiries or feedback.
