How To Use Slicer In Excel – Shiksha Online

The article discusses the use of Slicer in Excel to sort and filter huge datasets

Slicers are the visual representation of filters. It allows you to filter the data in the pivot table by simply clicking on the type of data you want. Slicer in Excel is very helpful if you have massive datasets. You can easily filter your datasets and pivot tables using Slicers.

Content

• How To Insert Slicer Into Your Spreadsheet?
• How to Add Multiple Slicers to Your Spreadsheet
• Getting Multiple Columns In a Slicer Box
• Adjusting Slicer Settings

How To Insert Slicer Into Your Spreadsheet?

Slicer in Excel can also be inserted into your Excel tables. Now, take a look at the typical data range below. You won’t see any Design option for this normal data range. Design is where a Slicer in Excel is located.

Read MS Excel Tutorials

 You would need to activate the option of Design and then Slicer in Excel and to achieve this, you should –

Step 1 – Select the entire dataset and press Ctrl + T to add a table to the data range.

Step 2 – Click OK, and it will create a table for you.

Step 3 – Once the table is created, you will see a new tab on the ribbon called Design. In this tab, you can see the Slicers option (only from Excel 2013 onwards).

Must Explore – MS Excel Courses

Step 4 – Select the Insert Slicer option. It will show you all available titles in the table.

Step 5- Select the required column that you want to filter data quite frequently.

Note: You can also select all titles. But I have chosen the Department column header.

This is the segmentation I’ve inserted for the Department header. It will list all the unique values ​​in the list. If you click on the data table for a specific Department, only the selected department’s data will be displayed.

I have selected Engineering, and the data table shows only the data for Engineering.

Now, I choose Business Development, and the data table shows data for Business Development.

How to Add Multiple Slicers to Your Spreadsheet

We can insert multiple slicers if we want to filter our data based on two columns. Each of the slicers tells the pivot table which subset of data to use to calculate the numbers.

I am using a dataset with 600 rows.

Step 1 – I will try to convert this data into a table. Here, I want to filter it on two parameters, Country and Department.  

Step 2 – I can quickly sort the data basis Country Name and Department. Here I have first selected China; the Slicer option threw me recommendations of the available departments in China. 

Step 3 – I then selected Marketing, and voila, I have the list of people working in the Marketing Department in China.

Getting Multiple Columns In a Slicer Box

By default, a Slicer has one column, and all items in the selected dimension are listed. In case you have many items, Slicer displays a scroll bar that you can use to review all the items.

You may want to have all the elements visible without the hassle of scrolling. You can do this by creating a multi-column slicer.

To do this:

• Select the Slicer.
• Go to Slicer Tools ⇒ Options ⇒ Columns.

• Change the value of Columns at your convenience. I have selected 8.

• This will instantly split the slicer elements into the required number of columns. However, you may find it messy as full names are not displayed. To make it look better, resize the Slicer and even its columns.

To do this:

• Select the Slicer
• Go to Slicer Tools ⇒ Options
• Change the height and width of the buttons and Slicer at your convenience.

Adjusting Slicer Settings

You can also change the setting of your Slicer using Slicer Setting in Options ⇒ Slicer Settings, or you can right-click on your Slicer and change from there.

Related Read – What Are Excel Errors and How To Fix Them?

You can –

• Change the caption
• Hide items with no data
• Sort and filter and Ascending or Descending order
• Hide header

I hope this article on Slicer in Excel will help you sort your huge datasets. It is an easy tool, and I recommend you practice it well before using it.

———————————————————————————-

Read More in Excel

MIS Reports in Excel | Excel Interview Questions | Basic Excel Formulas | Difference Between Formula and Function in Excel | Average Function in Excel | Introduction to MS Excel | HLOOKUP in Excel | ROW and COLUMN in Excel | Financial Modelling in Excel | Percentage In Excel | Remove Duplicates In Excel | Merge Cells in Excel | MIN and MAX Functions in Excel | Combine Text Strings in Excel | Import Data from PDF to Excel | Pivot Table In Excel | How To Enable Macros In Excel | How to Import Text Files to Excel | TRIM Function in Excel