Introduction to Excel

Jason Moore

This is a guide on the introduction to Microsoft Excel.

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

 

 

You should also read: