Excel Overview – Part 2

© Mike Splane 2017

Contents

Charts. 1

Elements of the Chart Screen. 1

Formatting icons. 1

The Charts Tools Design Tab. 1

Formatting Chart Elements using the right-click method. 1

Adding Chart Elements. 1

Functions. 1

SUM... 1

AVERAGE..........................................................................................................................

ROUND.. 1

TODAY()      

SUMPRODUCT. 1

SUMIF. 1

The Sum Function Icon. 1

Boolean Formulas. 1

IF Functions. 1

VLOOKUP Functions. 1

Data Validation. 1

Working with Large Sets of Data. 1

Sorting Data. 1

Creating a Filtered List. 1

Displaying Subtotals. 1

Pivot Tables. 1

Create a Pivot Table Report. 1

 

 

Charts

Elements of the Chart Screen

The picture shows my data range on the left, the column chart in the middle, and three formatting icons on the right. Two tabs for working with charts, Chart Tools, will appear in the ribbon above the chart. 

 

 

The names shown under the Horizontal X-axis are created from the column labels. The range of values shown on the Vertical Y-axis start with zero and go up to the biggest number found in the data.

In this chart, the Legend appears under the chart. It shows the names of each row of data and shows the color used to represent that row in the chart.

Each of the rows of data will appear in the chart with a different color. Each block of color represents a different data point. The Sandwich data appears as the blue part of each column. A set of data points sharing the same color is called a data series. 

 

Formatting icons

 

     Add/Edit Names and Labels

   Add/Edit Colors and Styles

   Add/Edit Chart Elements 

 

 

The Charts Tools Design Tab

 

 

The Change Colors icon is used to switch the set of colors that are used in the chart.

The Chart Styles icons are used to select a design for your chart.

The Switch Rows/Columns icon is used to reverse the X and Y axis layout of the chart.

The Select Data icon is used to alter the range of data you selected when you created the chart. The dialog box lists each row and column used to create the char. Un-checking a box next to a series name will hide that data and alter the appearance of the chart. If you selected the wrong data when creating your chart, you can select the name of a series and press the Delete icon to remove it.  On my experience it is usually easier to start over rather than trying to make corrections through this dialog box.

The Change Chart Type is used when you discover used chose the wrong type of chart when you created it.

The Move Chart icon is used o move the chart into a new or different worksheet.

 

Formatting Chart Elements using the right-click method

If you position your cursor above different parts of the chart, a label will appear, giving you the name of that chart element.  Try it. You can format any element in the chart by right-clicking on it. Select the Format option at the bottom of the menu.  A formatting dialog box for that element will appear to the right of the chart.

 

The icons stand for:

Fill & Line                    Special Effects                         Series Options

Line / Border               Shadow/Glow/Edges/3-D        Varies by type of chart.

 

Adding Chart Elements

The Add Chart Element icon on the Chart Tools Design tab (the name of this tab varies, different versions of Excel use different names) gives you a dropdown menu listing items (labels for example) to add or remove from the chart. In earlier versions of Excel, this set of options had its own tab in the ribbon.

 

 

Watch the video starting at 5:35 to see examples of how to use the chart elements options.  https://www.youtube.com/watch?v=KOOGje8W3P0       Time = 9:19

 

Functions

Excel has a built-in set of advanced formulas, called functions. Usually a function needs to be given one or more pieces of data (each piece is called an argument) before it will work. The arguments are listed inside parentheses following the name of the function. Each argument is separated by a comma.

Here are some commonly used functions:

SUM                            Example = SUM(C3:G9)

A range is the required argument.

Result: Total of all numbers in the range.

 

AVERAGE                    Example = AVERAGE(C3:G9)

                                                A range is the required argument.

Result: Average of all numbers in the range.

 

ROUND                        Example = ROUND(A4/3,2)

                                                A formula is the first argument.

The second argument is the number of decimal places to keep.

Result: if A4 = 5 then 5/3 = 1.6666 which is rounded to 1.6700.

 

TODAY()                      Example = TODAY()

                                                This function does not need an argument.                                           

Result: The current date is displayed.

 

SUMPRODUCT            Example = SUMPRODUCT(A1:B3,C1:D3)

This function multiplies the contents of each individual cell in one range by its counterpart in another equally sized range and displays the total.

The result is a single number. 

SUMIF                         Example = SUMIF(B1:B3,”>5”,C1:C3) This function searches the range given in the first argument to find cells which match the criteria given in the second argument. The values from the corresponding cells in the range in the third argument are summed. In our example the result  = 104 (91 plus14)

The Sum Function Icon

You can create Sum functions with the AutoSum icon (S) found on the Home tab. There are several different ways to create functions with the icon.

Boolean Formulas

You can write formulas in Excel to test the value in a cell. If the value matches the Boolean formula’s terms, Excel displays “True.” If they don’t match, “False” is displayed.

Examples:            = A1 < 4,               = A1 = “Red”                      = A1 < 10                             = A1 <> 5

These formulas will all evaluate as either True or False, depending on the contents of cell A1. Note that when you compare text strings, the string must be in quotes. Boolean formulas are used as a test condition in IF Functions, as described below.

IF Functions

The IF    function works with Boolean formulas. The words TRUE and FALSE that a Boolean displays are replaced with numbers, formulas, functions, or text strings. 

In Excel, an IF Function has three arguments.

The first argument is the Boolean formula that compares the contents of a cell to a value.

The second and third arguments give two options of what to display. If the Boolean formula evaluates as “True” the second argument is displayed. If the equation evaluates as “False” the third argument is shown.

Boolean

C3 is > 80

 

 

If True

 

Show “PASS”

 

If False

 

 

Show “FAIL”

We would write the function like this: =IF(C3>80,”PASS”,”FAIL”) Commas separate each argument.

If the value in cell C3 is greater than 80, then the condition is met. The word PASS will appear. If the value in cell C3 is NOT greater than 80 then the condition is not met.  The word FAIL will appear.

The second and third arguments in IF Functions can be:

You can even use an IF Function as an argument. This is called a nested IF.

Example: =IF(B9 = “Yes”, 21, IF(B10 = “Yes”, 22,23))

VLOOKUP Functions

VLOOKUP functions are used to find information from a table. For example, you could have a table showing the sales tax rates for different counties. You can give Excel the name of a county and it will show the tax rate for that county.

A VLOOKUP Function has three arguments and looks like this:  =VLOOKUP(C4,G5:H7,4)

The first argument is always a cell reference to the input cell where the user types in what to look up. Excel will look in the table for a match to the contents of the input cell and extract data from that row.

The second argument tells Excel the location of the table. It can be expressed as range notation or as a range name. The range includes the data and the row labels but not the column labels. The labels must be in the first column of the table and must be in alphabetical order.

The third argument, which is always a number, is the location of the column in the table which will contain the requested information. The first column in the table is designated as 1, the second is designated as 2, etc. 

Data Validation

A common problem with LOOKUP functions occurs when the entry in the input cell does not match anything in the table. Excel shows the closest match. To make sure the output data is always accurate you should prevent invalid entries into the input cell. Select the cell(s) where data entry should be restricted. On the Data tab, in the Data Tools group, click the Data Validation icon. On the Settings tab, under Validation Criteria, choose List in the Allow box. Then either type a list directly into the source box, using commas to separate each item in the list, or click on the icon in the Source Box. Then use the mouse to highlight the cells that contain your list. Press Enter and click OK.

Working with Large Sets of Data

Excel has database capabilities that are suitable for small and simple tasks. Microsoft Office includes a full database application called Access. Access can be used for tasks that require more complex data management capabilities.

A data table consists of one row of labels, called field names or headers, and several rows of data. The headers row is always the first row, above the rows of data. The rows of data should not contain any blank cells.

Tables should be separated from other areas in the worksheet by a blank row and a blank column. This helps Excel determine which cells are included in the database table. You can select any cell in the data table to work with the entire set of data; you do not have to select the entire table.

 

Sorting Data

You can use the A-Z or Z-A icons on the Data tab to sort data in a table. First click on any cell in a column that contains data. Then click on the A-Z or Z-A icon. Data in the selected column will be sorted. The other columns in the table will be rearranged so each row of data stays intact.

If your column contains numbers, the A-Z icon will put the numbers in order, starting with the smallest. The Z-A icon will put the numbers in order, starting with the largest.

You can use the Sort icon to simultaneously sort by more than one field. Click in the data. Click on the icon. Select a column to sort by and then select the order Click on Add Level to sort the data into subsets of the first selection. For example, you might want to sort by last name and then by first name.

 

Creating a Filtered List

Click on any cell in the data table. On the Data tab, select Filter. Pull-down arrows will appear next to the column headings. To hide some of the records, click on the pull-down arrow in a field. Every entry that has a checkmark next to it will be displayed. Remove the checkmarks for any records that you wish to hide.

 

Displaying Subtotals

You can do mathematical calculations on any columns that contain numbers. These calculations can be broken down to include subtotals for the results of similar records. Sort one of the columns that contain text, to group similar records. Click on any cell in the data table. On the Data tab select Subtotals. In the first dialog box select the column you just used to sort the data. Then select the type of calculation and the fields to work with.

 

Pivot Tables

Pivot tables give you tremendous flexibility in studying and manipulating large amounts of data. You can sort multiple fields of data using multiple criteria, and manipulate the data in several different ways. Pivot tables are particularly useful for analyzing marketing and manufacturing data involving multiple factors.

Create a Pivot Table Report

Click anywhere in the data table.

From the Insert tab, Tables group, click the Pivot Table icon to open the Create Pivot Table dialog box. Click OK.

The Pivot Table Field List should be open on the right side of the worksheet. To create the pivot table you choose fields and drag them from the top of the task pane into the boxes in the lower right corner. Fields containing text can be used as filters, as row names, and as column names.  Fields containing numbers are used in the values area.

You can add more than one field to any section.

You can add multiple instances of a single value field, and then customize them to show different mathematical operations: count, average, sum, etc.

If the task pane disappears, click on the pivot table to reopen it.