Bar Chart

A bar chart is the horizontal version of a column chart. Use a bar chart if you have large text labels.

To create a bar chart, execute the following steps.

• Select the range A1: B6.

• On the Insert tab, in the Charts group, click the Column symbol.

• Click Clustered Bar. 
  

• Result: 

Column Chart

Column charts are used to compare values across categories by using vertical bars.

To create a column chart, execute the following steps.

• Select the range A1: A7, hold down CTRL and select the range C1: D7.

• On the Insert tab, in the Charts group, click the Column symbol.

• Click Clustered Column.
  

• Result: 
  

Note: only if you have numeric labels, empty cell A1 before you create the column chart. By doing this, Excel does not recognize the numbers in column A as a data series and automatically places these numbers on the horizontal (category) axis. After creating the chart, you can enter the text Year into cell A1 if you like.

Line Chart

Line charts are used to display trends over time. Use a line chart if you have text labels, dates or a few numeric labels on the horizontal axis. Use a scatter chart (XY chart) to show scientific XY data.

To create a line chart, execute the following steps.

• Select the range A1: D7. 

• On the Insert tab, in the Charts group, click the Line symbol.

• Click Line with Markers.

• Result: 
  

Note: Only if you have numeric labels, empty cell A1 before you create the line chart. By doing this, Excel does not recognize the numbers in column A as a data series and automatically places these numbers on the horizontal (category) axis. After creating the chart, you can enter the text Year into cell A1 if you like.

Pie Chart

Pie charts are used to display the contribution of each value (slice) to a total (pie). Pie charts always use one data series.

To create a pie chart of the 2017 data series, execute the following steps.

Select the range A1: D2.

On the Insert tab, in the Charts group, click the Pie symbol.

Click Pie.

The result after choose pie.

Click on the pie to select the whole pie. Click on a slice to drag it away from the center.
The result after.

Note: Only if you have numeric labels, empty cell A1 before you create the pie chart. By doing this, Excel does not recognize the numbers in column A as a data series and automatically creates the correct chart. After creating the chart, you can enter the text Year into cell A1 if you like.

Showing Data As A Graphic

Unlike charts on an Excel worksheet, sparklines are not objects — a sparkline is actually a tiny chart in the background of a cell. The following picture shows a column sparkline in cell F2 and a line sparkline in F3. Both of these sparklines get their data from cells A2 through E2 and display a chart inside a cell that shows the performance of a stock. The charts show the values by quarter, highlight the high value (3/31/08) and the low value (12/31/08), show all the data points, and show the downward trend for the year.

Adding Sparklines

1. Select an empty cell or group of empty cells in which you want to insert one or more sparklines. 
2. On the Insert tab, in the Sparklines group, click the type of sparkline that you want to create: Line, Column, or Win/Loss. 
3. In the Data box, type the range of the cells that contain the data on which you want to base the sparklines.

Note: You can click to temporarily collapse the dialog box, select the range of cells that you want on the worksheet, and then click to restore the dialog box to its normal size.

About the Sparkline Tools Tab

When one or more sparklines are selected, the Sparkline Tools appear, displaying the Design tab. On the Design tab, you can choose one or more of several commands from among the following groups: Sparkline, Type, Show/Hide, Style, and Group.

Use these commands to create a new sparkline, change its type, format it, show or hide data points on a line sparkline, or format the vertical axis in a sparkline group. These options are described in detail in the following section.

Editing Sparkline Data

Control which value points are shown

You can highlight individual data markers (values) in a line sparkline by making some or all of the markers visible.

In this sparklines, the high-value marker is green, and the low-value marker is orange. All other markers are shown in black.

• To show all values, select the Markers check box.
• To show negative values, select the Negative Points check box.
• To show the highest or the lowest values, select the High Point or Low Point checkboxes.
• To show the first or the last values, select the First Point or Last Point checkboxes.

Change the style of or format sparklines

Use the Style gallery on the Design tab, which becomes available when you select a cell that contains a sparkline.

1. Select a single sparkline or a sparkline group. 
2. To apply a predefined style, on the Design tab, in the Style group, click a style or click the arrow at the lower right corner of the box to see additional styles.
3. To apply specific formatting to a sparkline, use the Sparkline Color or the Marker Color commands.

Show or hide data markers

On a sparkline that has the Line style, you can show data markers so that you can highlight individual values.

1. Select a sparkline. 
2. In the Show/Hide group, on the Design tab, select any of the checkboxes to show individual markers (such as high, low, negative, first, or last), or select the Markers checkbox to show all markers.

Clearing a check box hides the specified marker or markers.

Show and customize axis settings

• You can select Date Axis Type (in the Group group, click Axis) to format the shape of the chart in a sparkline to reflect any irregular time periods in the underlying data.

• In a line sparkline, applying the Date Axis type can change the slope of a plotted line and the position of its data points in relation to each other.
• In a column sparkline, applying the Data Axis type can change the width of, and increase or decrease the distance between the columns.

In the example shown here, there are two column sparklines that use data from the same range. The sparkline with the “Trend” label uses the General Axis type, and the sparkline with the “Trend (Data Axis Type)” label uses the Date Axis type. In each sparkline, the first two data points are separated by two months, and the second and third are separated by seven months. By applying the Date Axis type, the space between the three columns changes proportionally to reflect the irregular time periods.

You can also use these Axis options to set minimum and maximum values for the vertical axis of a sparkline or sparkline group. Setting these values explicitly helps you control the scale so that the relationship between values is shown in a more meaningful way.

1. With the sparkline or sparkline group selected, in the Group group, click Axis. 
2. Under Vertical Axis Minimum Value Options or Vertical Axis Maximum Value Options, click Custom Value.
3. Set minimum or maximum values that you feel will best emphasize the values in the sparklines.

You can increase the height of the row that contains the sparkline to more dramatically emphasize the difference in data values if some are very small and some are very large.

You can also use the Plot Data Right-to-Left option to change the direction in which data is plotted in a sparkline or sparkline group.

If there are negative values in your data, you can emphasize this by showing a horizontal axis in your sparkline.

1. With the sparkline or sparkline group selected, in the Group group, click Axis. 
2. Under Horizontal Axis Options, click Show Axis. Any sparklines that contain negative data will display a horizontal axis at 0.

Handle empty cells or zero values

You can control how a sparkline handles empty cells in a range (and thus how the sparkline is displayed) by using the Hidden and Empty Cell Settings dialog box.

Working With Tables And Filters

Sorting Data in a Table

• Quick sorting allows you to quickly and easily apply common sorting options to data within the table. Quick sorting can be applied to tables by clicking the pull-down arrow beside the name of the header you want to sort. 
• Then choose an ascending or descending sort option: 
  

• The values will now be sorted in ascending or descending order based on the criteria that you selected. 
• The pull-down arrow beside the header name will also show what type of sort has been applied. In the example below, you can see that the Weekly Sales column is now sorted by largest value to smallest: 

• You can also apply a quick sort by clicking Data → Sort A to Z or Sort Z to A:

• While using quick sorts is a simple way to apply basic sorting to your data, you are limited to sorting one column at a time. Additionally, quick sorts can only be used to sort columns, not rows.

What is an AutoFilter?

Use AutoFilter to find values, show or hide values, in one or more columns of data. You can filter based on choices you make from a list, search to find the data that you want to see. When you filter data, entire rows are hidden if values in one or more columns don't meet the filtering criteria.

1. Click the arrow in the column header and do one of the following: 
   
2. Select specific values: Check (Select All) to clear all of the check boxes, and then select only the values you want to see.
3. Search for values: In the Search box, type text or numbers you want to see. 
   
4. Click OK to apply the filter.

Creating a Custom AutoFilter

1. In the Custom AutoFilterbox, type or select the criteria for filtering your data. For example, to show all numbers between 1,000 and 7,000, in the is greater than or equal to box, type 1000, and in the is less than or equal to box, type 7000. 
2. Click OK to apply the filter.

Note: Comparison operators aren’t the only way to filter by criteria you set. You can choose items from a list or search for data. You can even filter data by cell color or font color. 

Using an Advanced Filter

If the data you want to filter requires complex criteria (such as Type = "Produce" OR Salesperson = "Davolio"), you can use the Advanced Filter dialog box.

• To open the Advanced Filter dialog box, click Data > Advanced.

The Advanced command works differently from the Filter command in several important ways.

• It displays the Advanced Filter dialog box instead of the AutoFilter menu. 
• You type the advanced criteria in a separate criteria range on the worksheet and above the range of cells or table that you want to filter. Microsoft Office Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the advanced criteria.

Using an Advanced Filter with Logical Statements

The criteria range can hold normal text, number or some logical statement.

Copying Filtered Records

By default, filtered records will be generated on the same worksheet as it’s an actual database. Once you got the filtered result, you can choose to duplicate or move the result to different worksheet or workbook.

Working with Tables

What is a Table

While formulas and functions are great at analyzing data within your workbook, they are more apt at analyzing entire workbooks rather than specific sets of data. If you need to analyze smaller sets of data within a large workbook or break down large sets of data into smaller parts, then converting your data into tables is often the best solution. Tables allow you to use Excel’s powerful organizational capabilities without modifying the data itself. Using tables you can narrow down specific data, focus on only the important information, and more.

Table

A table is a specially designated range of information that has added functionality. You can have multiple tables per worksheet, and tables can be as large or small as the amount of data you want to work with. Tables can be created from existing data ranges or from empty ranges and then populated afterward.

Once a table has been created, it will automatically be given a generic name such as “Table1” or “Table2” depending upon the number of tables present in the current worksheet. However, these names can be changed at any time.

Additionally, tables are flexible in that you can convert a table back to a normal range at any point without affecting the contents. 

A table is made from adjacent columns of data, with a unique label or heading for each column. Columns and rows may be added to a table just as you would when working with a normal range.

Remember that each worksheet has a lot more rows than columns. This design is well suited for data organized in long, adjacent, list-like columns:

Table Components

There are a number of components that come together to create a table. Some of these components, like the header row and banded rows, are required, but other components have the ability to be toggled on or off. Below you can see a table with many of the components toggled on:

Here is an overview of each element.

• The Header row (1) will display the column headers for each column in the table. For each header within the Header row, you will see the Header row drop-down arrow (2). This is used to access a variety of organizational commands. 
• Banded rows (6) allow you to easily differentiate between each row that appears within the table, while the Total row (3) is used to display the results of calculations that are done on a column-by-column basis. The Total row also includes the Total row drop-down arrow when selected. This gives you quick access to functions that can perform calculations on the table data. Note that this component will only be displayed if a cell in the Total row is selected. 
• Finally, the First column (4) option will apply a bold effect to the first column to the table to differentiate it from other columns. 
• The Last column (5) option, when applied, will do the same thing to the last column of data.

Creating Tables

The Create Table dialog box is used to convert existing ranges of data into a table. Additionally, this dialog box also allows you to choose if you want to include the top row of the selected range in the new table as a header row.

• To access this dialog, select the cell range that you would like to convert into a table and then click Insert → Table:

• The Create Table dialog will open. You will see the selected range (using absolute cell references) in the “Where is the data for your table?” text box:

• Clicking the OK button will convert the selected cell range into a table.

Modifying Tables

Similar to cell styles, table styles are preconfigured formatting options that can be applied to tables. They allow you to quickly apply a splash of color to your tables and in some cases enhance their readability. While you do have the option of configuring your own table style, you can also select from a variety of pre-configured quick styles. 

• You can find all of these quick styles within the Table Styles group of the Table Tools – Design tab:

• Clicking the More arrow within the Table Styles gallery will expand it to show more options:

• Clicking any style option inside the Table Styles gallery will apply it to the current table:

• To clear an applied style, click the More arrow within the Table Styles gallery, and then click Clear.
  
• The table will then be displayed with no style at all:

Using the Table Tools – Design Tab

Whenever you are working with a table, the Table Tools – Design contextual tab will appear on the ribbon. Using the controls on this tab, you can modify just about any aspect of your table:

Here is a brief description of the various groups that on this tab and the commands and options that they contain.

• Properties - This group allows you to view and edit the current table’s name. You can also redefine the table size using the Resize Table command. 
• Tools - Within this group, the Remove Duplicates command allows you to remove duplicate values from the current table. The “Summarize with PivotTable” command will create a PivotTable out of the current table, and the “Convert to Range” command will convert it back to a regular range. You can also insert a slicer into the table using the Insert Slicer command. 
• External Table Data - The commands in this group are used to export table data to other applications, as well as manage data links to external resources. 
• Table Style Options - The checkbox controls in this group allow you to toggle available table components on or off. 
• Table Style - This group displays a gallery of styles that you can apply to the current table.

Adding a Total Row

To insert a new row, select the table row below where you want to insert one or more blank table rows. Next, click Home → Insert → Insert Table Rows Above:

A new blank row will then be added above the selected row, shifting everything below this row downwards:

Similarly, you can add new columns by selecting the table column to the right of where you want to insert a new column. Next, click Home → Insert → Insert Table Columns to Left.

Using Functions in Excel

Working with Nested Functions

Using a function as one of the arguments in a formula that uses a function is called nesting, and we’ll refer to that function as a nested function.

For example, by nesting the AVERAGE and SUM function in the arguments of the IF function, the following formula sums a set of numbers (G2: G5) only if the average of another set of numbers (F2: F5) is greater than 50. Otherwise, it returns 0.

The AVERAGE and SUM functions are nested within the IF function.
You can nest up to 64 levels of functions in a formula.

Consolidating Data Using a 3-D Reference Sum Function

A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference.

Example: =SUM(Sheet2:Sheet13!B5)

Adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.

Using the IF Functions

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. In its simplest form, the IF function says: 

• IF(Something is True, then do something, otherwise do something else)

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

Example:

• =IF(C2=”Yes”,1,2)

In the example, cell D2 says: IF(C2 = Yes, then return a 1, otherwise return a 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 can 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 also nest multiple IF functions together in order to perform multiple comparisons.

Note: If you are going to use text in formulas, you need to wrap the text in quotes (e.g. “Text”). The only exception to that is using TRUE or FALSE, which Excel automatically understands.

Nested IF Functions

IF statements are incredibly robust and form the basis of many spreadsheet models, but they are also the root cause of many spreadsheet issues. Ideally, an IF statement should apply to minimal conditions, such as Male/Female, Yes/No/Maybe, to name a few, but sometimes you might need to evaluate more complex scenarios that require nesting* more than 3 IF functions together.

For your information, “Nesting” refers to the practice of joining multiple functions together in one formula.

While Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so. Why?

• Multiple IF statements require a great deal of thought to build correctly and make sure that their logic can calculate correctly through each condition all the way to the end. If you don’t nest your formula 100% accurately, then it might work 75% of the time, but return unexpected results 25% of the time. Unfortunately, the odds of you catching the 25% are slim. 
• Multiple IF statements can become incredibly difficult to maintain, especially when you come back sometime later and try to figure out what you, or worse someone else, was trying to do.

Below is an example of a relatively standard nested IF statement to convert student test scores to their letter grade equivalent.

• =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

This complex nested IF statement follows a straightforward logic:

1. If the Test Score (in cell D2) is greater than 89, then the student gets an A. 
2. If the Test Score is greater than 79, then the student gets a B. 
3. If the Test Score is greater than 69, then the student gets a C. 
4. If the Test Score is greater than 59, then the student gets a D. 
5. Otherwise, the student gets an F.