In this article we’ll describe two Microsoft Excel tips that can ease your workload. We’ll cover how to sort by selecting a cell and how to easily remove total rows from a worksheet.
How to sort by cell selection
Let’s say you have a worksheet with sales data for a small makeup company. We’d like to click on the name of a salesperson (say, Betsy) and have the data automatically filter to show only the rows where Betsy was the salesperson and mascara was the product. To enable filtering by selection, follow these steps:
- Add the AutoFilter icon to the Quick Access Toolbar. To do so, select FILE, OPTIONS, COMMANDS Not in the Ribbon and add AutoFilter to the Quick Access Toolbar.
- Move the cursor to any cell where Betsy is the salesperson and right click.
- Choose Filter and then Filter by selected cell’s value.
You will now see all the records where Betsy was the salesperson. Now, move to any row where the product was mascara and repeat the process. Your result should only show records where Betsy sold mascara, as shown in Figure 1.
Figure 1: Betsy selling Mascara
How to easily remove total rows from a worksheet.
This time, our hypothetical worksheet shows the sales of sandwiches, fruit, and desserts at a grocery store. You can see an example in Figure 2. There are also total rows showing the total sales for each product category.
Figure 2: Grocery Sales
We want to efficiently remove all the total rows – there could be hundreds of them! To remove the total rows, proceed as follows:
- Use CTRL+F to bring up the Find and Replace window and fill it in as shown in Figure 3.
Figure 3: Find and Replace window to find rows containing the word “total”
- Select Find All and click CTRL+A to select all cells containing the word “total.” Now click Close.
- Finally, select Delete from the Home tab and then Delete Sheet Rows. You have now deleted all Total rows.
For more Excel tips and tricks, take our Microsoft Excel Fundamentals + Data Analytics Certificate program. This program will strengthen your Excel skills, helping to make you an expert in a program that’s essential for accountants.
