Introduction to Excel

Jason Moore

This is a guide on the introduction to Microsoft Excel.

This is the Ankr Power Bank I have. It has been great and reliable when I go on trips or when I get on my laptop to write somewhere away from home.

Table of Contents

What is Excel

Excel makes it easy to crunch numbers. With Excel, you can streamline data entry with Autofill. Then get chart recommendations based on your data, and create them with one click. Or easily spot trends and patterns with data bars, color coding, and icons. To create a Workbook:

        1. Open Excel
        2. Select Blank Workbook or ctrl+n

 

Insert or Delete a Worksheet

In Excel, you can easily insert, rename, and delete worksheets in your workbook.

To insert a worksheet:

        1. Select the New Sheet plus icon at the bottom of the workbook
        2. Or, select Home > Insert > Insert Sheet

 

Rename a Worksheet

        1. Double click the sheet name on the Sheet tab to quickly rename it
        2. Or right-click on the sheet tab, select Rename then type a new name

 

Move a Worksheet

        1. To move the tab to the end, right click the Sheet tab then Reorder > move to end > Ok
        2. Or click and drag the tab to any spot

 

Delete a Worksheet

        1. Right click the Sheet tab and select Delete
        2. Or, in the ribbon menu, select the sheet, and then select Home > Delete > Delete Sheet

 

Move or Copy Worksheets

You can use the move or Copy Sheet command to move or copy entire worksheets, to other locations in the same or different workbook. You can use the Cut and Copy commands to move or copy a portion of the data to other worksheets or workbooks.

 

Select the worksheet tab and drag it to where you want it

 

When you move a sheet to another workbook, check any formulas or charts that refer to data on the sheet because moving the sheet might cause errors or produce unintended results in your data. Similarly, if you move a sheet of data that is referred to by 3-D references, the calculation might include or leave out data on the sheet.

 

Print a Worksheet or Workbook

You can print entire or partial worksheets and workbooks, either one at a time or several at once. Also, if the data that you want to print is in a Microsoft Excel table, you can print just the Excel table.

 

You can also print a workbook to a file instead of a printer. This can be useful when you need to print the workbook on a type of printer that is different from the one that you originally used to print it.

 

Before you print, it can be helpful to know about the many options available for an optimal print experience.

Some formatting, such as colored text or cell shading, may look good on the screen but not look ok when printed on a black and white printer. It may be helpful to print your worksheet with gridlines displayed so that the data, rows, and columns stand out better.

 

Print one or several worksheets:

        1. Select the worksheets that you want to print
        2. Select the File > Print > or press ctrl+p
        3. Optionally, adjust the Settings, then select the Print button

 

Print one or several workbooks:

All workbook files that you want to print must be in the same folder.

        1. Select file > Open
        2. Hold down ctrl, click the name of each workbook to print, and then select print

 

Print all or part of a worksheet

        1. Select the worksheet, then select the range of data to be printed
        2. Select the file, then click Print
        3. Under Settings, select the arrow next to Print Active Sheets and select the appropriate icon
        4. Select Print

 

Print an Excel table

        1. Select a cell within the table to enable the table
        2. Select File then Print
        3. Under Settings, select the arrow next to Print Active sheets and select Print Selected Table
        4. Select Print

 

Print a workbook to a file

        1. Select File then Print
        2. Under Printer, select Print to File
        3. Select Print
        4. In the Save Print Output As dialog box, enter a file name and then select Ok. The file will be saved in your Documents folder

 

Use Excel as your Calculator

Instead of using a calculator, use Excel to do the math

You can enter simple formulas to add, divide, multiply, and subtract two or more numeric values. Or use the AutoSum feature to quickly total a series of values without entering them manually in a formula. After you create a formula, you can copy it into adjacent cells. No need to create the same formula over and over again.

 

Learn about simple formulas

All formula entries begin with an equal sign. For simple formulas, simply type the equal sign followed by the numeric values that you want to calculate and the math operators that you want to use. Then Press Enter, and Excel instantly calculates and displays the result of the formula.

 

Although there is a Sum function, there is no Subtract function. Instead, use the minus operator in  a formula. Or, you can use a minus sign to convert a number to its negative value in the Sum function.

 

Use AutoSum

The easiest way to add a Sum formula to your worksheet is to use AutoSum. Select an empty cell directly above or below the range that you want to sum, and on the Home or formula tabs of the ribbon, click AutoSum. AutoSum will automatically sense the range to be summed and build the formula for you. This also works horizontally if you select a cell to the left or right of the range that you need to sum.

 

AutoSum does not work on non-contiguous ranges.

 

Intellisense function guide

The Sum floating tag beneath the function is its Intellisense guide. If you click the Sum function name, it will change to a blue hyperlink to the Hep topic for that function. If you click the individual function elements, their representative pieces in the formula will be highlighted.

 

Avoid rewriting the same formula

After you create a formula, you can copy it to other cells - no need to rewrite the same formula. You can either copy the formula, or use the fill handle to copy the formula to adjacent cells. When you copy the formula, ensure that the cell references are correct. Cell ranges may change if they have relative references.

 

Fill data automatically in worksheet cells

Use the autofill feature to fill cells with data that follows a pattern or are based on data in other cells.

        1. Select one or more cells you want to use as a basis for filing additional cells.

For a series like 1,2,3,4,5, type 1 and 2 in the first two cells

        1. Drag the fill handle
        2. If needed, click AutoFill Options and choose the option you want

 

Create a drop-down list

You can help people work more efficiently in worksheets by using drop-down lists in cells. Drop-downs allow people to pick an item from a list that you create.

        1. In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you will have your list items in an Excel table. If you do not, then you can quickly convert your list to a table by selecting any cell in the range and pressing ctrl+t

 

Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You do not need to do anything else.

 

        1. Select the cell in the worksheet where you want the drop-down list
        2. Go to the Data tab on the ribbon, and then Data Validation

 

If you cannot select Data Validation, the worksheet might be protected or shared. Unlock specific areas of a protected workbook or stop sharing the worksheet.

 

        1. On the Settings tab, in the Allow box, select List
        2. Select in the source box, then select your list range.
        3. If it is ok for people to leave the cell empty, check the Ignore blank box
        4. Check the In-cell drop-down box.
        5. Select the Input message box

 

If you want a message to pop up when the cell is selected, check the Show Input message when cell Is Selected box, and type a little message in the boxes.

 

        1. Select the Error Alert tab
        2. Pick an option in the style box

 

Move or Copy Cells, Rows, and Columns

When you move or copy rows and columns, by default Excel moves or copies all data that they contain, including formulas and their resulting values.

 

When you copy cells that contain a formula, the relative cell references are not adjusted. Therefore, the contents of cells and of any cells that point to them might display the #ref error value. If that happens, you can adjust the references manually.

 

You can use the cut or Copy command to move or copy selected cells, rows, and columns, but you can also move or copy them by using the mouse.

 

By default, Excel displays the Paste Options button. If you need to redisplay it, go to Advanced in Excel Options.

 

Using Commands to Copy and Paste

        1. Select the cell, row, or column on the Home tab. In the Clipboard group, select Cut or press ctrl+x
        2. To copy rows or columns, go to the home tab. In the Clipboard group, select Copy or press ctrl+c
        3. Right-click a row or column below or to the right of where you want to move or copy your selection, and then do one of the following: when moving rows or columns, select Insert Cut Cells or when you are copying rows or columns, select Insert Copied Cells.

 

To move or copy a selection to a different worksheet or workbook, select another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area. Excel displays an animated moving border around cells that were cut or copied. To cancel a moving border, press ESC.

 

Change Column Width and Row height

If you find yourself needing to expand or reduce Excel row widths and column heights, there are several ways to adjust them. If you are working in Page layout view, you can specify a column width or row height in inches, centimeters, and millimeters. The measurement unit is in inches by default. Go to File > Options > Advanced > Display > select an option from the Ruler Units. If you switch to Normal view, then column widths and row heights are displayed in points.

 

Individual rows and columns can have only one setting. For example, a single column can have a 25-point width, but it cannot be 25 points wide for one row, and 10 points for another.

 

Find or replace text and numbers

Use the Find or Replace features in Excel to search for something in your workbook, such as a particular number or text string. You can either locate the search item for reference, or you can replace it with something else. You can include wildcard characters such as question marks, tildes, and asterisks, or numbers in your search terms. You can search by rows and columns, search within comments or values, and search within worksheets or entire workbooks.

 

To find something, press ctrl+f, or go to Home > Editing > Find & Select > Find

        1. In the Find What box, type the text or numbers you want to find, or select the arrow in the find What box, and then select a recent search item from the list.
        2. Select find all or Find Next to run your search
        3. Select Options . To further define your search if needed
        4. If you want to search for text or numbers with specific formatting, select Format, and then make your selections in the Find format dialog box.

 

Replacing Text

To replace text or numbers, press ctrl+h, or go to Home > Find & Select > Replace

        1. In the Find What box, type the text or numbers you want to find, or select the arrows in the Find What box, and then select a recent search item from the list.
        2. In the Replace With box, enter the text or numbers you want to use to replace the search text
        3. Select the Replace All
        4. Select Options . To further define your search if needed
        5. If you want to search for text or numbers with specific formatting, select Format, and then make your selection in the find format dialog box

 

Merge and Unmerge cells

You cannot split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it.

        1. Select the cells to merge
        2. Select merge & center, then Merge Cells

 

When you merge multiple cells, the contents of only one cell appear in the merged cell. The contents of the other cells that you merge are deleted.

 

To unmerge cells

        1. Select the Merge & Center down arrow
        2. Select Unmerge Cells

 

You cannot split an unmerged cell. After merging cells, you can split a merged cell into separate cells again. If you do not remember where you have merged cells, you can use the Find command to locate merged cells quickly.

 

Apply data Validation to Cells

Use data validation to restrict the type of data or the values that users enter into a cell, like a drop-down list.

Select the cell that you want to create a rule for

        1. Select Data > Data Validation
        2. On the Settings tab, under Allow, select an option
        3. Under Data, select a condition
        4. Set the other required values based on what you chose for Allow and Data
        5. Select the Input Message tab and customize a message users will see when entering data
        6. Select the Show Input message when the cell is selected checkbox to display the message when the user selects or hovers above the selected cell or cells.
        7. Select the Error alert tab to customize the error message and to choose a Style
        8. Select Ok

 

Import or Export Text

There are two ways to import data from a text file with Excel: you can open it in Excel, or you can import it as an external data range. To export data from Excel to a text file, use the Save As command and change the file type from the drop-down menu.

 

There are two commonly used text file formats:

        1. Delimited text files(.txt), in which the tab character typically separates each field of text.
        2. Comma separated values(.csv), in which the comma character typically separates each field of text.

 

You can change the separator character that is used in both delimited and .csv files. This may be necessary to make sure that the import or export operation works the way that you want it to.

 

Importing a Text file

You can open a text file that you created in another program as an Excel workbook by using the Open command. Opening a text file in Excel does not change the format of the file - you can see this in the Excel title bar, where the name of the file retains the text file extension.

        1. Go to File > open and browse to the location that contains the text file
        2. Select Text Files in the file drop-down list in the open dialog box.
        3. Locate and double-click the text file that you want to open.

 

Import a text file by connecting to it

You can import data from a text file into an existing worksheet

        1. Click the cell where you want to put the data from the text file
        2. On the Data tab, in the Get & Transform Data group, click From Text/CSV
        3. In the import Data dialog box, locate and double click the text file that you want to import, and click Import
        4. After selecting the table, select Load > Load To, to specify where you want to put the data.

 

Export data to a text file by saving it

You can convert an excel worksheet to a text file by using the save As command

        1. Go to File > Save As
        2. The Save As dialog box appears
        3. In the Save as type box, choose the text file format for the worksheet
        4. Browse to the location where you want to save the new text file, and then click save
        5. A dialog box appears, reminding you that only the current worksheet will be saved to the new file. If you are certain that the current worksheet is the one that you want to save as a text file, click OK. You can save other worksheets as separate text files by repeating this procedure for each worksheet.
        6. A second dialog box appears, reminding you that your worksheet may contain features that are not supported by text file formats. If you are interested only in saving worksheet data into the new text file, click yes. If you are unsure and would like to know more about which Excel features are not supported by text file formats, click help for more information.

 

 

Formatting

In Excel, you can format numbers in cells for things like currency, percentages, decimals, dates, phone numbers, or social security numbers.

        1. Select a cell or a cell range
        2. On the Home tab, select Number from the drop-down menu
        3. Select the format you want. To see all available number formats, select the dialog box launcher next to Number on the Home tab in the Number group.

 

General Format

The default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way you type them. However, if the cell is not wide enough to show the entire number, the General format rounds the numbers with decimals. The General number format also uses scientific notation for large numbers.

 

Number Format

Used for the general display of numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

 

Currency Format

Used for general monetary values and displays the default currency symbol with numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

 

Accounting Format

Also used for monetary values, but it aligns the currency symbols and decimal points of numbers in a column.

 

Date Format

Displays date and time serial numbers as date values, according to the type and locale that you specify. Date formats that begin with an asterisk respond to changes in regional data and time settings that are specified in the Control Panel. Formats without an asterisk are not affected by Control panel settings.

 

Time Format

Displays date and time serial numbers as time values, according to the type and locale that you specify. Time formats that begin with an asterisk respond to changes in regional date and time settings that are specified in the Control Panel. Formats without an asterisk are not affected by Control Panel settings.

 

Percentage Format

Multiplies the cell value by 100 and displays the result with a percentage symbol. You can specify the number of decimal places that you want to use.

 

Fraction Format

Displays a number as a fraction, according to the type of fraction that you specify.

 

Scientific Format

Displays a number in exponential notation, replacing part of the number with E+n, where E multiplies the preceding number by 10 to the nth power. For example, a 2-decimal Scientific format displays 12345678901 as 1.23+10, which is 1.23 times 10 to the 10th power. You can specify the number of decimal places that you want to use.

 

Text Format

Treats the contents of a cell as text and displays the content exactly as you type it, even when you type large numbers.

 

Special Format

Displays a number as a postal code, phone number, or social security number

 

Custom Format

Allows you to modify a copy of an existing number format code. Use this format to create a custom number format that is added to the list of number format codes. You can add between 200 and 250 custom number formats, depending on the language version of Excel that is installed on your computer.

 

Align or Rotate Text in a Cell

If you want to change the way data appears in a cell, you can rotate the font angle, or change the text alignment.

        1. Select a row, column, or range
        2. Select Home > Orientation, and then select an option

 

Rotate text to a precise angle

        1. Select a row, cell, column, or range
        2. Select Home > Orientation > Format Cell Alignment
        3. Under Orientation on the right side, in the Degrees Box, use the up or down arrow to set the exact number of degrees that you want to rotate the selected cell text

 

Align a row or column

Begin by selecting the cell or cells you want to align. You can align text in a single cell, a range of cells, or a row or rows, a column or columns, or the entire worksheet.

        1. Select the cells you want to align
        2. On the Home tab, in the Alignment group, select a horizontal alignment option
        3. On the Home tab, in the Alignment group, select a vertical alignment option

 

Change the Format of a cell

You can apply formatting to a cell or group of cells and the data therein. You can think of it as a cell being the frame in which the data is held.

 

To change cell formatting without using predefined styles, take these steps:

        1. Select a cell or multiple cells
        2. In the Home ribbon Font area, select from options such as Bold, Font color, or Font Size.

 

Apply Excel Styles

        1. Select the cells
        2. In the Home ribbon Styles area, select Cell Styles
        3. Select from the available style options

 

Modify an Excel Style

        1. Select the cells that have an Excel style applied
        2. Right click the applied style in Home > Cell Styles
        3. Select Modify > Format to modify the applied style

 

Copy Cell Formatting

        1. Select the cell with the formatting you want to copy
        2. Select Home > Format Painter
        3. Drag to select the cell or range you want to apply the formatting to
        4. Release the mouse button and the formatting should now be applied

 

Add a Watermark in Excel

You can use an image, such as a logo, to insert as a watermark that will appear on the spreadsheet along with the displayed data.

 

Create a Watermark

        1. Select insert > Head & Footer
        2. Tap on the header and on the Header & footer elements tab on the ribbon, click Picture
        3. Select one of the available options to insert your image. In the header, you will see & Picture.
        4. Tap anywhere outside the header to see the watermark

 

Format the Watermark Picture

        1. Click the header in the spreadsheet
        2. On the header & Footer elements tab, click Format Picture
        3. In the Format Picture dialog box, select Picture tab > Color > select an option

 

Display or Hide Zero Values

You may have a personal preference to display zero values in a cell, or you may be using a spreadsheet that adheres to a set of format standards that requires you to hide zero values. There are several ways to hide zero values.

 

In newer Office desktop versions and Microsoft 365 subscription, sometimes you might not want zero values showing on your worksheets; sometimes you need them to be seen. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.

 

Hide or Display All Zeros

        1. Go to file > Options > Advanced
        2. Under Display options for this worksheet, select a worksheet

 

Hide zero Values in selected Cells

These steps hide zero values in selected cells by using a number format. The hidden values appear only in the formula bar and are not printed. If the value in one of these cells changes to a nonzero value, the value is displayed in the cell, and the format of the value will be similar to the general number format.

        1. Select the cells that contain the zero values that you want to hide
        2. You can press ctrl+1, or on the Home tab, select format > Format Cells
        3. Select Number > Custom
        4. In the Type box, type 0; -0;;@, and then select Ok

 

Display Hidden Values

        1. Select the cells with hidden zeros
        2. You can press ctrl+1, or on the Home tab, select Format > Format Cells
        3. Select Number > general to apply the default number format, and then select Ok

 

Hide Zero Values Returned by a Formula

        1. Select the cell that contains the zero value
        2. On the Home tab, select the arrow next to Conditional formatting > Highlight Cells Rules > Equal to
        3. In the box on the left, type 0
        4. In the box on the right, select custom format
        5. In the Format Cells box, select the Font tab
        6. In the Color box, select white, and then select Ok

 

Display Zeros as Blanks or Dashes

Use the If Function to do this.

Use a formula like this to return a blank cell when the value is zero:

=IF(A2-A3=0,"",A2-A3)

Here is how to read the formula. If 0 is the result of (A2-A3), do not display 0 - display nothing(indicated by double quotes). If that is not true, display the result of A2-A3. If you do not want the cells blank but want to display something other than 0, put a dash "-" or other character between the double quotes.

 

Hide Zero Values in a PivotTable Report

        1. Select the PivotTable report
        2. On the Analyze tab, in the PivotTable group, select the arrow next to Options, and then select Options
        3. Select the layout & format tab

 

Create a Custom number Format

Create and build a custom numeric format to show your numbers as percentages, currency, dates, and more.

        1. Select the numeric data
        2. On the Home tab, in the Number group, select the small arrow to open the Dialog box
        3. Select Custom
        4. In the type List, select an existing format, or type a new one in the box
        5. To add text to your number format, type what you want in quotation marks and add a space to separate the number and text
        6. Select Ok

 

Overview of Formulas

Create a formula that refers to values in other cells.

        1. Select a cell
        2. Type the = sign
        3. Select a cell or type its address in the selected cell
        4. Enter an operator
        5. Select the next cell or type its address in the selected cell
        6. Press Enter. The result of the calculation appears in the cell with the formula.

 

When a formula is entered into a cell, it also appears in the Formula bar.

To see a formula in the formula bar, select a cell

Enter a formula that contains a built-in function:

        1. Select an empty cell
        2. Type an equal sign = and then type a function. For example, =SUM for getting total sales
        3. Type an opening parenthesis (
        4. Select the range of cells, and then type a closing parenthesis )
        5. Press Enter to get the result

 

Xlookup function

Use the Xlookup function to find things in a table or range by row. For example, loop up the price of an automotive part by the number, or find an employee name based on their employee ID. With Xlookup, you can look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on.

 

The Xlookup function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, the Xlookup can return the closest match.

 

Vlookup

Use vlookup when you need to find things in a table or a range by row. For example, loop up the price of an automotive part by the part number, or find an employee name based on their employee ID.

 

In its simplest form, the Vloopup functions says:

=Vlookup(what you want to look up, where you want to look for it, column number in the range containing the value to return, return an approximate or exact match - indicated by 1 or 0.

 

The secret to Vloopup is to organize your data so that the value you loop up is to the left of the return value you want to find. If you are a Microsoft Copilot subscriber, Copilot can make it even easier to insert and use Vlookup or Xlookup functions.

 

There are four pieces of information that you will need in order to build the Vlookup function syntax:

        1. The value you want to look up, also called the lookup value
        2. The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for Vlookup to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.
        3. The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.
        4. Optionally, you can specify True if you want an approximate match of False if you want an exact match of the return value. If you do not specify anything, the default value will always be True or approximate match.

 

Now put all of the above together as follows:

=Vlookup(lookup value, range containing the lookup value, column number in the range containing the return value, approximate match (True) or exact match (False)).

 

Sum Function

The Sum function adds values. You can add individual values, cell references, ranges, or a mix.

For example:

=Sum(A2:A10) Adds the values in those cells

=Sum(A2:A10,C2:C10) Adds the values in those cells

 

Countif Function

Use Countif, one of the statistical functions, to count the number of cells that meet a criterion, for example, to count the number of times a particular city appears in a customer list.

 

In its simplest form, Countif says:

=Countif(where do you want to look, what do you want to look for)

 

For example, :

=Countif(A2:A5,"London")

=Countif(A2:A5,A4)

 

Be aware that Countif ignores upper and lower case in text strings. Use wildcard characters. Make sure your data does not contain erroneous characters. For convenience, use named ranges.

 

The Countif function will not count cells on cell background or font color. However, Excel supports user-defined functions using VBA operations on cells based on background or font color.

 

If Function

The If function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. So, an If statement can have two results. The first result is if your comparison is true, the second if your comparison is false.

 

For example, =If(c2="Yes",1,2) says If(C2=Yes, then return 1, otherwise return 2)

 

=If(C2=1,"Yes","No")

In this example, the formula in cell D2 says: If(C2=1, then return Yes, otherwise return No). As you see, the If function can be used to evaluate both text and values. It can also be used to evaluate errors. You are not limited to only checking if one thing is equal to another and returning a single result, you can also use mathematical operators and perform additional calculations depending on your criteria. You can nest multiple If functions together in order to perform multiple comparisons.

 

=If(C2>B2,"Over Budget"."Within budget")

In the above example, the If function in D2 is saying If(C2 is greater than b2, then return "Over Budget", otherwise return "Within Budget")

 

=If(C2>B2,C2-B2,0)

In the above example, instead of returning a text result, we are to return a mathematical calculation. So, the formula in E2 is saying If(Actual is Greater than Budgeted, then Subtract the budgeted Amount from the Actual Amount, otherwise return nothing)

 

=If(E7="Yes",F5*0.0825,0)

In this example, the formula in F7 is saying If(E7="Yes", then calculate the Total Amount in F5*8.25%, otherwise no Sales Tax is due so return 0)

If you are going to use text in formulas, you need to wrap the text in quotes. The only exception to that is using True or False, which Excel automatically understands.

 

IFS Function

The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first True condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

 

Generally, the syntax for the IFS is:

=IFS(Something is True 1, Value if True 1, Something is True 2, Value if True 2, Something is True 3, Value is True 3])

Please note that the IFS function allows you to test up to 127 different conditions. However, we do not recommend nesting too many conditions with IF or IFS statements. This is because multiple conditions need to be entered in the correct order, and can be very difficult to build, test, and update.

 

The formula for cells A2:A6 is:

=IFS(A2>89,"A", A2>79,"B", A2>69,"C",A2>59,"D",TRUE,"F")

This says IF(A2 is greater than 89, return an "A", IF A2 is greater than 79, then return a "B", and so on for all other values less than 59, return an "F")

 

The formula in cell G7 is:

=IF(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)

This says IF(the value in cell F2 equals 1, then return the value in cell D2, IF the value in cell F2=2, then return the value in cell D3, and so on, finally ending with the value in cell D8 if none of the other conditions are met).

 

To specify a default result, enter True for your logical_test argument. If none of the other conditions are met, the corresponding value will be returned.

 

SumIf Function

You use the SumIf function to sum the values in a range that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only  the values that are larger than 5. You can use the following formula:

=SumIf(B2:B5,">5")

 

If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5,"John",C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John"

The SUMIF function returns incorrect results when you use it to match strings longer than 255 characters or to the string #VALUE!.

 

Match Function

The Match function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5,25, and 38, then the formula =Match(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

 

Use Match instead of one of the Lookup functions when you need the position of an item in a range instead of the item itself. For example, you might use the Match function to provide a value for the row_num argument of the Index function.

 

Syntax : Match(lookup_value, lookup_array, [match_type])

The match function syntax has the following arguments:

        1. Lookup_value: The value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The lookup_value argument can be a value or a cell reference to a number, text, or logical value.
        2. Lookup_array: The range of cells being searched
        3. Match_type: The number -1,0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

 

Create and Format Tables

This will show you how to create and format a table to visually group and analyze data.

        1. Select a cell within your data
        2. Select Home > Format as Table
        3. Choose a style for your table
        4. In the Create Table dialog box, set your cell range
        5. Mark if your table has headers
        6. Select Ok

 

Sort Data in a Table

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. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

 

You can sort data by text, numbers, and dates and times in one or more columns. You can also sort by a custom list you create or by format, including cell color, font color, or icon set. To find the top or bottom values in a range of cells or tables, such as the top 10 grades or the bottom 5 sales amounts, use AutoFilter or conditional formatting.

 

Filter Data in a Range or Table

Use AutoFilter or built-in comparison operators like 'greater than' and 'top 10' in Excel to show the data you want to hide the rest. Once you filter data in a range of cells or tables, you can either reapply a filter to get up-to-date results, or clear a filter to redisplay all of the data.

 

Use filters to temporarily hide some of the data in a table, so you can focus on the data you want to see.

 

Filter a range of data:

        1. Select any cell within the range
        2. Select Data > Filter
        3. Select the column header arrow
        4. Select Text Filters or Number Filters, and then select a comparison, like Between
        5. Enter the filter criteria and select Ok

 

Filter data in a table:

        1. Select the column header arrow for the column you want to filter
        2. Uncheck (Select All) and select the boxes you want to show
        3. Select Ok. The column header arrow changes to a filter icon. Select this icon to change or clear the filter.

 

Total the Data in an Excel Table

You can quickly total data in an Excel table by enabling the total Row option, and then use one of several functions that are provided in a drop-down list for each table column. The Total Row default selections use the Subtotal function, which allows you to include or ignore hidden table rows, however you can also use other functions.

        1. Click anywhere inside the table
        2. Go to table tools . Design, and select the check box for total Row
        3. The total Row is inserted at the bottom of your table
        4. Select the column you want to total, then select an option from the drop-down list. In this case, we applied the Sum function to each column.

 

If you want to copy a total row formula to an adjacent cell in the total row, drag the formula across using the fill handle. This will update the column references accordingly and display the correct value. If you copy and paste the formula in the total row, it will not update the column references as you copy across, and will result in inaccurate values.

 

Use Slicers to Filter Data

Slicers provide buttons that you can click to filter tables or pivot tables.. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand why what exactly is currently displayed.

 

You can use a slicer to filter data in a table or PivotTable with ease.

        1. Click anywhere in the table or PivotTable
        2. On the insert tab, press Enter
        3. In the Insert Slicers dialog box, select the check boxes for the fields you want to display, then select Ok
        4. A Slicer will be created for every field that you selected. Clicking any of the slicer buttons will automatically apply that filter to the linked table or PivotTable.

 

Charts

Create a chart: Charts help you visualize your data in a way that creates maximum impact on your audience.

        1. Select data for the chart
        2. Select Insert > Recommended Charts
        3. Select a chart on the Recommended Charts tab, to preview the chart
        4. Select a chart
        5. Select Ok

 

Add a trendline:

        1. Select a chart
        2. Select Chart Design . Add Chart Element
        3. Select trendline and then select the type of trendline you want, Linear, Exponential, linear Forecast, or Moving Average.

 

Add or Remove Titles in a Chart

To make a chart easier to understand, you can add chart titles and axis titles, to any type of chart. Axis titles are typically available for all axes that can be displayed in a chart, including depth axes in 3-d charts. Some chart types have axes, but they cannot display axis titles. You cannot add axis titles to charts that do not have axes.

 

Add a chart tile:

        1. In the chart, select the chart title box and type in a title
        2. Select the + sign to the top right of the chart
        3. Select the arrow next to Chart title
        4. Select Centered overlay to lay the title over the chart, or more Options for additional choices.
        5. Right-click the chart title to format it with options like Fill or Outline

 

Remove a chart title:

        1. Click on the chart
        2. Select the = sign next to Chart title
        3. Uncheck the checkbox next to Chart Title

 

Show or Hide a Chart Legend

You can show or hide the legend of a chart. Showing a legend can give readers more information about the chart, while hiding a legend can give your chart a cleaner look.

 

Showing a chart legend:

        1. Select a chart and then select the plus sign to the top right
        2. Point to Legend and select the arrow next to it
        3. Choose where you want the legend

 

Hide a chart legend:

        1. Select a legend to hide
        2. Press delete

 

Add or Remove a Secondary Axis

When the numbers in a chart vary widely from data series to data series, or when you have mixed types of data, plot one or more data series on a secondary vertical axis. The scale of the secondary axis shows the values for the associated data series. A secondary axis works well in a chart that shows a combination of column and line charts. You can quickly show a chart like this by changing your chart to a combo chart.

        1. Select a chart to open Chart Tools
        2. Select Design > Change Chart Type
        3. Select Combo > Cluster Column - Line on Secondary Axis
        4. Select Secondary Axis for the data series you want to show
        5. Select the drop-down arrow and choose Line
        6. Select Ok

 

Add a Trend or Moving Average

Add a trendline to your chart to show visual data trends

 

Add a trendline:

        1. Select a chart
        2. Select the + to the top right of the chart
        3. Select Trendline
        4. In the Add Trendline dialog box, select any data series options you want, and click Ok

 

Format a trendline:

        1. Click anywhere in the chart
        2. On the Format tab, in the Current Selection Group, select the trendline option in the dropdown list
        3. Click Format Selection
        4. In the format Trendline pane, select a Trendline option to choose the trendline you want for your chart. Formatting a trendline is a statistical way to measure data
        5. Set a value in the forward and Backward fields to project your data into the future

 

Add a moving average:

        1. Click anywhere in the chart
        2. On the format tab, in the current selection group, select the trendline option in the dropdown list
        3. Click Format selection
        4. In the format Trendline pane, under Trendline options, select moving Average. Specify the points if necessary

 

Update Data in an Existing Chart

If you need to change data in a chart, you can do it from its source.

 

Changes you make will instantly show up in the chart. Right click the item you want to change and input the data - or type a new heading - and press enter to display it in the chart.

 

To hide category in the chart, right click the chart and choose select Data. Deselect the item in the list and select Ok. To display a hidden item on the chart, right click and Select Data and reselect it in the list, then choose Ok.

 

Use Sparklines to Show Data Trends

A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight max and min values. For best impact, position a sparkline near its data.

 

Add a sparkline:

        1. Select a blank cell at the end of a row of data
        2. Select Insert and pick sparkline type, like Line or Column
        3. Select cells in the row and Ok in menu
        4. More rows of data. Drag handle to add a sparkline for each row

Filtering Data in Excel

In Excel 365, you can choose to show or hide different data types of data which is known as filtering. If you do not need a particular category or type of data, you can specify criteria to include or exclude, or indicate the value that you want to visualize.

 

In my worksheet below, I currently have no filters applied. To add filters to each of my column headers with the Home tab selected, go to the Editing group on the far right of the tools ribbon and click the Sort & Filter dropdown, and from the menu you can select the option Filter. I will click away to close this.

 

Alternatively, you can access the filter option by switching to the Data tab, then go to the Sort and Filter group on the right hand side of the ribbon and click the filter ribbon. When you do this, filter options displayed as dropdown buttons are now displayed to each of my column headers.

 

Now, for example, if I would like to filter the companies found in this worksheet, I can click the company dropdown button, and in the menu are the filter options so sort your data alphabetically, by color, and at the bottom are the options Text Filters; if you are searching for values found within your column, you can use the search bar and type in the value that you would like to find. Then there is a list of the particular values found within this column. To quickly filter out specific data, in the list you can uncheck the select all option and then go through the list and individually select the values you would like to view. If I scroll down this list and hide, for example, only to view the values related to Zebra Sky, I can check the box next to that option and then click the Ok button at the bottom center.

 

The values displayed in my table have now reduced, and only the data relating to the company zebra Sky is displayed. You will also see that the company dropdown button has a Filter icon displayed in it, indicating that a filter has been applied. If I would like to edit this filter, I can select the Company dropdown button to open the Filter options again, and from the list at the bottom, I can add values, for example, Homotic, by checking the box next to it, then click the Ok button.

 

I have not modified the filter criteria and data values relating to the company Homotic also shown in my table. If you would like to clear the filter values, you can do that by clicking the Clear option in the Sort & Filter group of the data tab. This has quickly cleared the applied filter and sort options from the selected data range. You can also apply multiple column filters. So I will select the Purchase type dropdown button, then uncheck the option select All, and from the list select the option Bedroom, then click the Ok button.

 

I can filter this data further. For example, I only want to see this data for the company homotic, so I will click the Company dropdown button, uncheck the Select All, and then check the option Homotic. To apply this filter, click the Ok button. As you can see, this will help you refine your data to find the precise values that you are looking for. If your data is continually updating and is dynamic, you will need to reapply your filters. To do this, go to the Sort & filter group in the data tab and click the reapply option. This ensures that your data is being continually filtered when you insert new data.

 

Now to clear my applied filters, I am going to choose clear in the top right hand corner of the sort & filter group. To remove my filters, I will click the filter option in the sort & filter group, and all filters and sort options applied within my worksheet have been removed.

Applying Multiple Filters in Excel

Column filters can be useful if you would like to quickly show or hide different data values in a data range in Excel, but you can apply multiple filters to one or more columns of a worksheet to refine the data further.

 

With the Data tab selected, if I go to the Sort & Filter group on the right hand side of the tools ribbon, I can see that the filter is highlighted, indicating that filtering is activated. On the right hand side of each column header cell is a dropdown button, which can be used to filter the data in this worksheet.

 

In my worksheet, for example, I wanted to filter out a specific value found in the Purchase type column, I can click the dropdown button in this header to open the filter menu. At the bottom of this menu is a search field, which you can use to search for a specific value. Rather than scrolling through a list of values below this, I will click inside the field and type in the value "kitchen". The list of values below updates and now displays the options Select All Search Results, Add current selection to filter, and then my value Kitchen. I will leave both the first and third options checked, and I will leave the option Add current selection to filter unchecked. As this is the first filter I am going to apply. To apply this, click the Ok button at the bottom center. The filter Kitchen has now been applied to the Purchase type column in my worksheet.

 

I can add an additional filter to this already filtered column. To do this, I will click the Purchase type dropdown button again, then go to the search field at the bottom of the filter menu, click inside it and type in the value "bedroom". Again, the list below updates. Now, since I already have a filter applied to this column, I am going to check the box next to the option Add current selection to filter, then click the Ok button. As you can see, I now have the two filtered items Kitchen and bedroom applied to this particular column.

 

Now, I can again add another filter to the Purchase type column by clicking the dropdown button, and this time I will go to the list of values at the bottom of the filter menu and manually select one of the options. I will check the box next to Bathroom, then click the ok button, and the filter bathroom has been applied to the selected column.

 

You can also remove any of these filters. Click the dropdown button to open this column's filter menu. To remove all of the filters applied to this particular column, I can select the Clear filter from Purchase type option. To remove just one filter from this column, go to the list below and uncheck the filter value you would like to remove. So I will uncheck the box next to Bathroom, then click the Ok button. If you would like to remove all of the filters that you have applied to your worksheet, switch to the Data tab. Go to the sort & filter group and select the option clear, and all applied filters have been removed.

Building Filter Arguments in Excel

In Excel 365, filter arguments can be used to query the data within a cell range and show values that meet defined filter criteria, such as equals, contains, and between.

 

In my worksheet, if I am looking to filter according to text, for example the Surname column, I can easily add a text filter. To do this, click this column's filter dropdown button to open the filter menu, go to the bottom of the menu and hover over the option Text Filters. In the side menu that opens on the right are multiple arguments that you can apply to find your specific text value, such as Equals, Does not Equal, Begins with, Ends with, Contains, Does Not Contain, and custom Filter. For example, if I am looking for a value that is equal to a text value found within this column, I will select the option Equals.

 

A Custom AutoFilter dialog box opens. At the top left is a drop down, and in it is the equals argument I selected. You can change this if you wish by clicking the dropdown button and selecting another argument from the menu. To the right of this is a field where you can type in the specific value that you are looking for. You can also click this field's dropdown. For example, I will select the value baily, then click the ok button at the bottom corner.

 

My data values have now been quickly filtered by text, and my worksheet displays the data with the surname Baily. To clear this filter, click the surname's filter dropdown button and select the option Clear filter From Surname in the menu.

 

You can also filter values according to a numeric argument. To demonstrate this, I will go to the Purchase total column, which has numerical values in it and click its dropdown button. At the bottom of the menu, instead of the option Text filters, you now have Number Filters, as it has switched to match the content type of the column. Hover over this to open the side menu where you have different argument options such as Equals, Does Not Equal,, Greater Than, Greater Than or Equal, Less Than or Equal To, Between, Top 10, Above Average, Below Average, and Custom Filter. This time, I will select does Not Equal. The Custom AutoFilter dialog box opens and Does Not Equal argument has been inserted into the top left field. In that field, to the right of this, I will type in the value 150, then click the Ok button. My worksheet updates to now only show all of the values not equal to 150 dollars.

 

Quickly switch to the Data tab, and I will select the Clear option in the sort & Filter group to remove any filters applied. You can also create a custom filter for your text or numerical values. For example, if I go back to my text values  in the Surname column, click its dropdown button and from the filter menu hover Text Filters, at the bottom of the side menu you will notice that you have the option Custom filter. Click this to open the Custom AutoFilter dialog box. From the Custom Filter menu, you can choose your specific arguments at the top left. So I will click this dropdown, scroll down the list and select the option 'begins with'. I will click inside the field to the right of this and type in the letter c. If I want to filter multiple arguments at the same time, I can either select the And option or the Or option on the left hand side. Since I want to add another 'begins with' letter, I will select the option Or. Click the dropdown below this, scroll down the menu and select my argument 'begins with'. To the right of this, click inside the field and I will insert my second letter b, then click the ok button. So now you can see that the surnames starting with C and B have been automatically filtered out of my data values. I am going to go to the sort & filter group and select the Clear option.

 

You can also filter a numeric range according to a custom argument. I will click the Purchase dropdown, hover over Number filters, and from the side menu select the argument Greater Than. The custom AutoFilter dialog box opens with the argument 'greater than' selected at the top left. Click in the field to the right, and I will type in the minimum values for my range, 150. Since I am creating a range, on the left I am going to be sure of the option And is selected. Click the dropdown at the bottom left and select the argument 'less than'. Click in the field to the right of this and insert the value 200, then click the Ok button. So now I can see that the values between 150 and 200 dollars have been filtered out of the specific data range.

Performing Advanced Filtering in Excel

In Excel 365, you can use a separate table to create advanced filter criteria. Once you have defined the filter criteria, you can launch a filter by defining the data range and selecting cells you want to use as criteria. These criteria cells can be combined as much as you want to create powerful filter operations.

 

At the top left corner of my worksheet is a mini table where I want to add specific criteria to filter out of my larger table below. Now, the data you would like to find will depend on the table above. For example, I have chosen the three separate columns Purchase total, Company, and Purchase type in my mini table. I can use the criteria found in my data table below to conduct an advanced filter.

 

After I have added my filter criteria, I am going to need to access the advanced filter options. To do this, I will first click outside of my data in the worksheet, then I am going to switch to the Data tab. On the right hand side of the tools ribbon, go to the Sort & Filter group and select the option Advanced.

 

The Advanced filter dialog box opens. At the top, I have two Action options. I can filter the list in place, or I can copy to another location. I am going to leave the option filter the list in Place selected. Below this, you will be able to see your list range. Excel has automatically detected my table as my list, but if this is incorrect, you can select the cell selection icon on the right hand side of the field and then choose your corresponding range.

 

Just below, I am going to need to define my criteria range. Since I have established my criteria in my mini table above, I am going to need to scroll back up the worksheet, and I am going to select the cell range B1 to C3, which includes the table headers, as well as the criteria cells I entered in earlier. Once I have selected my criteria, at the bottom left I can check the box next to the option to include unique records only. Once I have selected my advanced filter options, I will click the Ok button at the bottom center, and I can see that my data has been filtered out in my table below according to the criteria established in my mini table at the top left.

 

Now, if I would like to quickly clear this filter, I can go back to the Sort & Filter group in the tools ribbon and select the Clear option, and the filter has been removed from the second table. Depending on how specific you want to be, the more filter criteria that you define in the advanced filter criteria table, the more the results will be refined.

Filtering and Sorting Using Colors in Excel

If you have applied colored fields to various data entries in a table, you can use those colors to filter and sort the data. You might, for example, want to visualize only the values that have been highlighted in red.

 

Before I sort or filter my data by color, I first need to make sure that filters have been applied to my column headers and that a background fill color has been applied to my data values. To apply filters, with the Home tab selected, go to the Editing group on the far right of the tools ribbon, click the sort & Filter dropdown and select filter from the menu.

 

Dropdown filter buttons now appear on the right hand side of each of my column header cells, which can be used to filter the worksheet data. Now, if I want to filter my values according to color, I can click one of the header filter dropdown buttons. I will click the Company dropdown button to open the filter menu. In the center of this menu, hover over the filter by Color option. A side menu opens on the right. The options displayed in this menu may differ depending on how many and which type of colors you have applied to your data cells. In this menu are the two color options green and salmon, which I have applied to my cells, as well as the option No Fill.

 

Note that if you were to select the option to No fill, this would filter the values that have no fill color applied, which would only display the values that are not coded and hide the values with the color fill. If I want to filter my values by the color green, I can select that option in the menu and I will be able to see that only the data values colored green appear in my worksheet.

 

To remove the applied filter, I am going to switch to the Data tab, go to the Sort & Filter group on the right hand side and select the Clear option. The filter is now removed from the table.

 

You can also sort your data by color. To do this, I will click the Surname filter dropdown button, and from the top of the filter menu, hover over the option Sort by Cell Color. A side menu then opens on the right with my two color options, green and salmon, and the options No Fill and Custom Sort. I want to change my sort order to the salmon color located in my worksheet, so I will select that color option in the side menu. Now, all of the data values that are filled in using the color salmon appear at the top of my worksheet. You can apply a different sort by Color if you wish. I will click a filter dropdown button, hover over the Sort by Color option in the filter menu, and from the side menu, if this time I were to select the color green, my values would be sorted first by the color green, then by the color salmon, followed by all of the values that are not color coded.

 

If I select the option No Fill, my table has now been sorted so that all of the values not filled in with a color appear at the top. If I scroll down, next are my values colored in salmon, and finally the data values that have been filled in green.

Sorting Data in a Worksheet in Excel

In Excel 365, you can use sort options to organize and order the data in a table. You can specify the criteria that you want to use. You can, for example, sort a data range using alphabetical order or from smallest to largest value.

 

To quickly sort the data in a worksheet, with the Home tab selected, I can go to the Editing group on the far right of the tools ribbon. Within the Editing group, you will find your Sort & Filter options. Now, the sort options available will depend on the cell you have selected in your worksheet. Here, I have cell B5 selected, which contains text data.

 

So, if I click the Sort & Filter dropdown button, in the menu are the options to sort the data alphabetically from A to Z, to sort it from Z to A, and the option Custom Sort. If I select cell C3, which contains a numerical value, and I click the Sort & Filter again, this time in the menu you have the options to sort the data from smallest to largest, sort it from larges to smallest, and the option Custom Sort again. I will click away to close this menu.

 

These sort options are also available via the Data tab. So, if I switch to the Data tab, on the right hand side of the tools ribbon is the Sort & Filter group, and on the left of this group are the sort options Smallest to Largest displayed as an A-Z icon, and the option Largest to Smallest displayed as a Z-A icon. I will click the Sort Smallest to Largest button. The values in my worksheet have now been rearranged, as the data in column C has been sorted into smallest to largest.

 

Not that you can also do the reverse and sort from largest to smallest by clicking the Sort Largest to Smallest below this. If I now select cell B3, which contains a text value, and then go to the sort options in the sort & Filter group, at the top left of the group is the option to sort alphabetically from A to Z, and below this is the option to sort from Z to A. So, if I click the Sort A to Z button, the data in my worksheet has once again been rearranged, as the text values in column B have been sorted alphabetically from A to Z.

 

You also have the option to create your own custom sort. For example, I would like to sort the date values located in column G, so I will select the cell G3 within this column, then go to the Sort & Filter group and click the Sort button.

 

A sort dialog box opens. In this box, the sort options are now categorized by Column, Sort on and Order, so I can go sort by a specific column by clicking the Sort by column dropdown, and from the menu select one of the column headings. So I will select the option Order date. To the right of this, you can then choose what you would like to sort on. Click the Sort on dropdown and in the menu you can sort Cell values, Cell color, Font Color, or the Conditional Formatting icon. I will select the option Cell Values.

 

Lastly, you will need to choose your sort order. Click the Order dropdown, and since I have a date column selected, in the menu are the options from Oldest to newest, Newest to Oldest, and the option Custom List. I will select Newest to Oldest. To apply these sort conditions, click the Ok button at the bottom right corner, and the values in the Order date column have been sorted from newest to oldest.

Applying Multiple Sort Levels in Excel

In Excel 365, you can create multiple sort levels to change how data is presented, and you can create data groups within a cell range. You can also reorganize these levels to adjust how your data is grouped when sorting.

 

First, from your Home tab towards the right hand side of the tools ribbon is the Editing group, where you will find the sort & Filter dropdown button. Click this and in the menu is the option Custom Sort. You can also access the Custom Sort option directly from the data tab, and in the Sort & Filter group on the right hand side of the ribbon, click the Sort button. The Sort dialog box opens.

 

In this box, the first sort level is available to be specified using the Sort by Column, sort On, and Order options. To add another sort level, click the Add Level button at the top left corner of the window. Another sort level called Then By has been added below the Sort by level in the window. When creating your sort levels, you need to choose the column that you would like to sort by. To do this for the first sort level, I will click the Sort by column dropdown, and from the list I will select the column header Purchase type. To the right of this, you can choose your Sort On option. If a text column has been specified, you will see that the default sort option is Cell Values. Click the Sort On dropdown, and in the menu are the options to sort on Cell Values, Cell Color, Font Color, or Conditional formatting icon. I will select the option Cell Values.

 

Next, you can choose your sort order. Again, if a text column is selected, by default the sort order is from A to Z. Click this dropdown, and you also have the option to sort from Z to A. But if the selected column has numeric values, the order options will be from smallest to largest and largest to smallest. I will select the option A to Z. I will now do the same for the Then by sort level. Click the Column dropdown and select the column Purchase total. I am happy with the default sort on option Cell Values. I will click the Order dropdown and this time select the option Largest to Smallest.

 

If you would like to save time creating your sort levels, you can copy them. To do this, first select the sort level you would like copied. Then, at the top center of the dialog box, click the Copy level button. A new Then by level has been added below the other two sort levels. As you can see, you can create multiple levels quickly by selecting the copy Level option. A copied level is editable using the Column, Sort On and Order dropdown menus, same as demonstrated earlier with the other two levels.

 

You can also change the order in which your sort levels are applied using the up and down arrow buttons at the top center of the dialog box. So, if I would like to move the last sort level to be applied first, with that level selected, I will click the up arrow button twice to move it to the top of the list. The same goes if you want to move a level down the list.

 

First, select your level, then click the down arrow to move it down the list. If you would like to apply a specific option to a sort level, you can select that level, then click the Options button at the top right of the dialog box. This opens the Sort options dialog box, which contains the option to make the sort level case sensitive and to change the orientation to sort either from top to bottom or from left to right. When you are happy with your selections, you can click the Ok button at the bottom left. I will close this window by clicking the X icon at the top right corner.

 

Finally, you can also remove a sort level that you no longer need. To do this, first select the sort level that you are no longer using, then select the Delete level button at the top left of the dialog box, and the selected level has been deleted from the list. To apply the sort levels to your worksheet data, select the Ok button at the bottom right corner. So now you can see that I have multiple sorts applied to my data. I can see that my values within the Purchase total column take precedence over my Purchase type values.

Create and Use a Custom Sort List in Excel

When sorting data, you can use or create custom sort lists for data that does not sort alphabetically. This is particularly useful if your data contains days of the week or months of the year, or if you are sorting data that features text-based ranking systems such as high, low, and urgent priorities.

 

When sorting data, you can use or create your own custom sort list. Now in column D of my worksheet, I have a list of dates. I would like to organize these dates by month. I can easily do this by first selecting a cell within column D and then with the Home tab selected towards the right hand side of the tools ribbon within the Editing group, click the Sort & Filter dropdown button. From the menu select the custom Sort option.

 

The sort dialog box opens. On the left, I can click the sort by column dropdown and choose my Date of birth column from the list. To the right of this is sort on. I am going to leave the option Cell Values selected. Now, on the right hand side, I will click the Order dropdown. Since it detected that this is a date column, in the menu are the options to sort by Oldest to Newest or Newest to Oldest. I do not want to use either of these options, so I will click on Custom List at the bottom of the menu. The Custom Lists dialog box opens.

 

On the left of this window is a Custom lists box containing default custom lists that you can apply to your data ranges. For example, here I have the option to apply abbreviated days of the week, full days of the week, or abbreviated months or full months. For example, I will select the line that displays the months in an abbreviated form. That option now appears in the List entries box on the right of the window. To apply this new list, select the Ok button at the bottom right.

 

The Custom list dialog box closes and the Order field in the Sort dialog box displays the names of the months in their abbreviated form. To apply the sort, click the Ok button at the bottom right.

 

Rather than customizing by months or days, I can also create my own custom list. I would like to do this for my Purchase type column, so I will select a cell within column F. to do this, I will click the Sort & Filter dropdown in the Editing group and select the option Custom Sort, then click the Sort by dropdown on the left of the Sort window and select Purchase type from the list. Then click the Order dropdown on the right and select the option Custom List at the bottom of the menu. The Custom Lists dialog once again displays. To create a new custom list, select New List on the left hand side, and then insert your list entries on the right hand side.

 

To create your entries, you are going to need to separate each one by a comma. I want the value Kitchen to appear first as it is the most important item, so I am going to type in "Kitchen", followed by a comma and then a space, and I can continue entering my values using this format. After I have created my entries, I can click the Add button on the right. Those list entries now appear in the Custom Lists box. You can modify these entries at any time by selecting the List entries field and making changes as needed. Once my list entries have been created, I will click the Ok button at the bottom right to add my list. The Order field in the Sort dialog box now displays my custom list.

 

So now the Purchase type column will be ordered with the value Kitchen first, followed by Bedroom, Bathroom, and Living room. To apply this sort, click the Ok button. The sort dialog box closes and the sort selections have been applied to column F. A custom sort list can also be removed by clicking the Sort & Filter dropdown in the tools ribbon and select Custom Sort.

 

On the right of the Sort dialog box, click the Order dropdown and select the option Custom List to open the Custom Lists dialog box. On the left, choose the list you would like to delete, then click the Delete button on the right hand side. When you do this, a notification will display stating that this will be a permanent deletion. To continue click the Ok button, and my custom list no longer appears in this window. Close this by clicking the Ok button at the bottom right, and close the Sort dialog box by clicking the  X icon at the top right.

Performing an Advanced Sort in Excel

Excel 365 offers a number of additional tools that you can use to change how you sort your data. You can include or exclude header data in your operation, change the sort orientation from vertical to horizontal and apply case sensitive sorting.

 

Now that you have seen how to add a sort to your data, you can access the advanced sort settings to refine your sorting even further. To access the advanced sorting options, you will need to first switch to the Data tab, then go to the Sort & Filter group in the tools ribbon and click the sort button. The Sort dialog box then opens.

 

When you are applying a sort, you will see above that you have a few different options. Note that at the top right of the window, by default the option My Data has Headers checkbox is selected, which is necessary to ensure the column headers are not rearranged along with any sorted cell values during a sort operation.

 

To access further tools that you can use to sort your data, click the Options button at the top right of the window. The Sort Options dialog box displays. At the top is the option Case sensitive. If you enable this option, this will rearrange the data based on whether it is lowercase or uppercase. This is useful if you have data values that may appear in lowercase as well as uppercase. In order to organize my values according to case, I will check the box next to this option. Below that you have your orientation options. If you have a worksheet with column headers, by default it is set to sort your data from top to bottom, but you can also set it to sort from left to right. I will leave it set to Sort from top to bottom. To apply these changes, click the Ok button at the bottom left. The sort Options dialog box closes.

 

Now, in order to view what your case sensitive values will look like when you add a sort, you can click Sort by column or Sort by row if you change your orientation, and from the menu I will select the Company column. To the right of that, I will click the sort on dropdown, and from the menu you can sort on Cell Values, Cell color, Font color, or Conditional Formatting icon. I will leave it set to Call Values, so I will click away to close this. Then I am going to click the Order dropdown menu, and here I have the option A to Z. If you have applied the Case sensitive feature, uppercase letters will precede lowercase letters if the order category A to Z is selected. If you select the order category Z to A, lowercase letters will precede uppercase letters in the sort. I will select the option A to Z.

 

Once you are done choosing your sort criteria, click the Ok button in the bottom right corner to apply your sort. The sort dialog box closes, and the selections made have been applied to the Company column in my worksheet. Now, I can see that the data in my company has been sorted in the order A to Z, as well as lowercase if I have added any to those in particular column values.

Hiding and Removing Duplicate Values in Excel

Excel 365 has a filter that allows you to temporarily hide duplicate values. If you do not want to keep that information, Excel features a Remove duplicates command that can be used to find and delete values that exist in a data table or cell range.

 

 I have a spreadsheet open in Excel. Some of the rows have been duplicated. If you want to simply hide duplicate elements, first select the range to verify - in this case, I will click and drag the entire table. Once the table is selected, I click on the Data tab, and in the sort & Filter tools, I click on Advanced.

 

The Advanced Filter window opens, and in the Action section I filter the list, in place, selected, I leave List Range the same and I check Unique Records Only, then I click Ok. Now in the table, two rows have been hidden. This could be confirmed in the numbers, because now it goes from 4 to 7, since 5 and 6 are hidden. I can use the keyboard shortcut ctrl-z to undo this action, and this time I can remove the duplicate items.

 

With the data selected again, and in the Data tab, I go to the Data Tools toolset. Then I click on Remove duplicates, and the Remove duplicates window opens. We have the columns listed, and they are currently all checked. You can simply uncheck any of the columns by clicking on the checkbox, or you can use the buttons. We have the columns listed, and they are currently all checked. You can simply uncheck any of the columns by clicking on the checkbox, or you can use the buttons. Unselect all unchecks all of the columns, while select all checks all of the columns. Then I click the Ok button and Excel notifies me that 2 duplicate values were found and removed and three unique values remain.

 

You should also read: