How to Work With Formulas in Microsoft Excel

How to Sort and Filter Your Data in Microsoft Excel

You can more easily organize and view data in Excel by sorting and filtering it.

You may use Excel to keep track of lists and other information, maybe people, products, expenses, bills, taxes, and more. But sometimes you need a way to sort and filter all the data in each spreadsheet so you can better arrange what you view or just peek at specific records. And that’s exactly what Excel offers: You can sort the data in each column alphabetically or numerically, from smallest to largest or largest to smallest. You can even add multiple levels to sort the data in more than one way.

If sorting doesn’t quite do the trick, you can filter the data. Filtering adds a dropdown menu to each column so that you can choose which data you want to see:

• You can choose to see data equal to, greater than, or less than a certain value.
• You can opt to see only records with blank fields.
• And you can adopt custom filters.

But figuring out how to use sorting and filtering properly can be a challenge unless you know your way around both features. Let’s look at the many ways to sort and filter your data in Excel.

For this article I’m using Excel 2016, but these sorting and filtering instructions work the same in prior versions of Excel as well.

Begin by launching Excel and opening or creating a spreadsheet with a list of items.

Note that you’ll want to use a spreadsheet with multiple rows and columns, with each column starting with a header cell that describes the contents of that column. Make sure at least one of the columns contains numbers and another contains dates.

On my end, I’m working with a spreadsheet of expenses with the name of each expense in the first column, the dollar amount in the second column, the due date in the third column, an indication of whether the bill’s been paid in the fourth column, and how the bill was paid in the fifth column.

Let’s say you want to sort the data in the first column alphabetically from A-Z. Put your cursor in any cell of the first column. Click on the Sort & Filter button on the Home Ribbon and select the option to Sort A-Z. The rows are now resorted so that data in the first column appears alphabetically.

Click on the Sort & Filter button again and this time choose the option to Sort Z-A. The rows are resorted so the data in the first column goes from Z-A.

Now we want to sort the spreadsheet by numbers from smallest to largest. Move your cursor into any cell except the header cell of whichever column contains numbers. Click on the Sort & Filter button and select Sort Smallest to Largest. The spreadsheet now is sorted by the numbers in that column.

Click on the Sort & Filter button and select Sort Largest to Smallest, and now the spreadsheet is sorted by numbers from largest to smallest. Play around with the columns, choosing a cell in each column and selecting the different sort options to see how the spreadsheet is rearranged. A column of dates will offer options to sort from oldest to newest or newest to oldest.

Okay, let’s amp things up.

Let’s say you have the same data for dollar amount or date or other information in different records in your spreadsheet. In that case, you may want to sort the data based not just on one criterion but on two or more criteria.

For example, you can sort by dollar amount first and then by date second and by then name third. Let’s try this.

Make sure your spreadsheet contains the same value in more than one row, such as the same amount and date for multiple rows. Click on the Sort & Filter button and select Custom Sort. In the Custom Sort window, choose the first criterion by which you want to sort the data, such as by dollar amount. Make sure the sort is based on value and that the order is smallest to largest. Click on the Add Level button.

For the second level, select the date and change the order to oldest to newest. Click on the Add Level button once more.

For the third level, select the name of the expense or other item and make sure the order is set to A-Z. Click OK.

You should see your spreadsheet rearranged based on the multiple levels of criteria that you set. Return to the Custom Sort window. Your sort still appears. You can now make any changes to it and then reapply the sort.

Now let’s segue to filtering. Place your cursor in any column. Click on the Sort & Filter button and select Filter. Notice that your header row displays a dropdown arrow in each cell. Click on that arrow in any header cell in a column that contains numbers. You can still sort your list from smallest to largest or vice versa by selecting the appropriate sort order.

But you can do more than that. You can uncheck any entries you don’t want to see in the column. You can search for specific entries (handy if your spreadsheet is very big). You can also customize the filter. Move your cursor to the entry for Number Filters. From the flyout menu, select Equals.

At the Custom AutoFilter window, click on the dropdown list next to the word Equals and select a number that appears more than once in your spreadsheet. Click OK, and now you see only records with that specific number.

Click on the arrow in the header cell for that column and go back to Number Filters and then Equals. You can now add a second criterion to the filter.

In the second field, choose the option for is greater than or equal to. Click on the dropdown list for this level and choose another number. Change the function word from And to Or. Click OK. Your spreadsheet shows you only the results that match the two criteria you set up.

Again, click on the down arrow in the header cell. This time check the option for Select All. Click OK, and your spreadsheet displays all the records again.

Let’s try one more. Click on the arrow in a header cell for a column that contains blanks. Uncheck the entry for Blanks. Click OK. Your spreadsheet now shows only records with no blank cells in that column.

Again, click on the arrow in the header cell. This time, check the entry Blanks but uncheck all other entries. Click OK, and your spreadsheet shows only records with blanks in that column.

Finally, click on the arrow once more, check Select All, click OK, and your spreadsheet displays all records again.

How to Work With Formulas in Microsoft Excel

You can do a lot more in Excel than just add up numbers.

Excel is designed to help you create formulas to perform a variety of different calculations and tasks. But formulas can be intimidating. You probably already know how to auto sum numbers and handle basic calculations.

But there’s more to formulas than just the basics. Here’s what you can do with them.

• You can multiply, subtract, divide, and average numbers.
• You can see the minimum and maximum numbers in a column or row.
• You can include different types of calculations in a single formula.
• You can also use absolute cell references in your formulas to keep certain numbers constant.

Let’s look at how to work with formulas in Excel.

I’m using Excel 2016 as my test subject, but the steps I describe here apply equally to the previous few versions of the program.

Launch Excel and create a spreadsheet with eight columns and five rows. Type the word Product in cell A1. Type the name of an item in each other cell in Column A and a date in each other cell in Row 1. Leave the last cell in each column free for totaling the numbers above. Enter numbers into all the other cells, so you should have numbers in cells B2 through H5.

Make sure all the number cells are formatted as numbers without decimal points. To do this, select and right-click the cells with numbers, select Format Cells from the menu, select Number from the list of categories, and then click OK.

Now place your cursor in the empty cell in Column B below the cells of numbers. Click on the AutoSum icon on the Home Ribbon. Confirm that the selection includes all the numbers above to be totaled. Press Enter to trigger the AutoSum function.

Move to the last cell in Column B.

Let’s try a different formula for this column. Instead of adding the numbers, you want to see an average of them.

Click on the right arrow next to the AutoSum icon and select Average. Press Enter. That cell displays the average of the numbers above.

Try some of the other formulas accessible from the AutoSum menu. Place your cursor in another empty cell. Click on the right arrow next to the AutoSum icon and select Count Numbers to see the total number of cells with numbers in that column.

Move to the next empty cell and click on the right arrow next to AutoSum. Max shows you the largest number in the column or row, while Min shows you the smallest number.

Maybe we want to subtract certain numbers. Move your cursor to an empty cell. Type =. Then select one of the cells with a number. Notice that the cell reference, e.g., B4, is added to the formula. Type a – sign. Then select a cell with another number to add its cell reference to the formula. Press Enter, and the resulting formula subtracts the second number from the first.

Now let’s try multiplying and dividing. Move your cursor to an empty cell. Type = and select a cell with a number. Type *. Then select another cell with a number and press Enter. This cell shows the result of the two numbers multiplied. Move to another empty cell. Type = and select a cell. Type /. Select another cell and press Enter. This result shows one number divided by the other. Note that the resulting cell may round off the number so you don’t see the exact figure.

To change this, right-click on the cell and select Format Cells. Select Number from the list of categories and change the decimal points to 4. Click OK, and now you should see a more exact result.

You can also increase or decrease the decimal point more quickly via the Ribbon. Move to the Number section on the Ribbon. Click on the Increase Decimal icon to add decimal points and the Decrease Decimal icon to reduce decimal points.

You can combine different calculations in a single formula. Move your cursor to an empty cell. Type =. Select a cell. Type +. Select another cell. Type -. Select another cell. Type *. Select another cell. Press Enter. The resulting cell shows the total calculation.

However, it may not be the calculation you want. Remember from math class in school that calculations of different types are performed in a certain order, as per the order of operations, and not simply from left to right. So looking strictly at numbers, multiplication and division come first and then addition and subtraction.

In your formula, you may want to ensure that the addition and subtraction is performed before the multiplication. You can easily correct that by using parentheses. Click on the formula in the Formula Bar. Put an open parenthesis in front of the first cell reference and a close parenthesis after the third cell reference. Press Enter, and now you see a different result because the addition and subtraction were calculated before the multiplication. You can now delete the formula in that cell.

Here’s one final trick with formulas.

You may already know that after you create a formula in a cell, you can use autofill to automatically copy that formula to the next several cells. And when you do that, the cell references become relative. For example, let’s say you have the following formula in a cell: =B2-C2. When you copy that formula to the cell to the right, the formula changes to =C2-D2 to refer to the cells in that column.

But sometimes you may not want relative cell references in a formula. Sometimes you may want one of the numbers in a formula to stay constant.

Here’s how to set that up in your spreadsheet. Move to cell B9. Type =. Select cell B2. Type -. Then select cell C2. Press Enter. Now grab cell B9 by the lower right until you see the plus symbol. Drag it to cell C9. Check the formula in C9, and you’ll see it says: =C2-D2. But instead, we want the first number in the formula to always be the number in cell B2. Delete the result in cell C9 and move back to B9.

Click on the formula for that cell in the Formula bar. Type a $ in front of the B and a $ in front of the C. So the formula should read: =$B$2-C2.

Now drag the cell by the lower right to C9 and to the rest of the cells in that row. Check the formula in C9 and the other cells, and you’ll see that it retains the reference to B2 but changes the second cell reference to the appropriate cells in each column.