Excel OfficeExcel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.
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…. read more »
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…. read more »
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… read more »
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… read more »
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… read more »
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… read more »
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… read more »
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… read more »
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… read more »
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… read more »