Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. This example teaches you how to sort data by color in Excel. 1. Click any single cell inside a data set. 2. On the Data tab, in…
Tag: Sort and Filter
Reverse List in Excel
This chapter teaches you an easy way to reverse a list in Excel. For example, we want to reverse the list in column A below. 1. Enter the value 1 into cell B1 and the value 2 into cell B2. 2. Select the range B1:B2, click the lower right corner of this range, and drag it down to cell B8. 3. Click any number in the…
Randomize/ Shuffle List in Excel
How to randomize / shuffle a list in Excel. For example, we want to randomize the list in column A below. 1. Select cell B1 and insert the RAND() function. 2. Click on the lower right corner of cell B1 and drag it down to cell B8. 3. Click any number in the list in column B. 4. To sort in descending order, on…
Number and Text Filters Examples in Excel
How to apply a number filter and a text filter to only display records that meet certain criteria. Follow the steps below: 1. Click any single cell inside a data set. 2. On the Data tab, in the Sort & Filter group, click Filter. Arrows in the column headers appear. Number Filter To apply a number filter, execute the following steps. 3. Click the…
Filter Data Based on Date in Excel
This example teaches you how to apply a date filter to only display records that meet certain criteria. 1. Click any single cell inside a data set. 2. On the Data tab, in the Sort & Filter group, click Filter. Arrows in the column headers appear. 3. Click the arrow next to Date. 4. Click on Select All to clear all the…
Use Data Form to input, edit and delete records in Excel
The data form in Excel allows you to add, edit and delete records (rows) and display only those records that meet certain criteria. Especially when you have wide rows and you want to avoid repeated scrolling to the right and left, the data form can be useful. 1. Open the downloadable Excel file. 2. Add the Form command to the Quick Access Toolbar. 3. Click…
Add Outline to Data in Excel
Outlining data makes your data easier to view. In this example we will total rows of related data and collapse a group of columns. 1. First, sort the data on the Company column. 2. On the Data tab, in the Outline group, click Subtotal. 3. Select the Company column, the column we use to outline our worksheet. 4. Use the Count function. 5. Check…
Subtotal function in Excel
The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use. The SUBTOTAL function is designed for columns of data, or vertical ranges. Use the SUBTOTAL function in Excel instead of SUM, COUNT, MAX, etc. to ignore rows hidden by a filter or manually hidden rows. Rows Hidden by a Filter 1.…
Advanced Filter in Excel
Filter setting using — And Criteria, Or Criteria or Formula as Criteria This example teaches you how to apply an advanced filter in Excel to only display records that meet complex criteria. When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range (blue border below for illustration only) above your data set. Use the same column headers. Be…
Remove Duplicates Example in Excel
When duplicates are removed in Excel, the first occurrence of the value in the list is kept, but other identical values are deleted. This example teaches you how to remove duplicates in Excel. Go here to only find duplicates. 1. Click any single cell inside the data set. 2. On the Data tab, in the Data Tools group, click Remove Duplicates. The following dialog…
How To Filter Data in Excel
Filter your Excel data if you only want to display records that meet certain criteria. By filtering data in a worksheet, you can find values quickly. You can filter on one or more columns of data. With filtering, you can control not only what you want to see, but what you want to exclude. You can filter based on choices you make…
How To Sort One Column or Multiple Columns in Excel
You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order. Navigation: Data Tab → Sort & Filter Group → Sort One Column To sort on one column, execute the following steps. 1. Click any cell in the column you want to sort. 2. To sort in ascending order, on the Data tab, in the Sort & Filter…
How To Remove Duplicates In Excel Column Or Row?
Remove Duplicate Rows Using the Excel Remove Duplicates Command. In Excel, the Remove Duplicates command can help you to remove duplicated records in a column. The Remove Duplicates command is located in the ‘Data Tools’ group, within the Data tab of the Excel ribbon. Follow the steps below: Data Tab → Data Tools Group → Remove Duplicate Command icon Remove duplicate rows…