Data Display

Selecting Data to Create a Chart#

You know how to start already: Creating charts begins as if you were preparing to sort or filter. Maybe, you need to sort first. So do that, then begin again, and start by selecting all adjacent data exactly as you did with sorts and filters. Even if the instuctions are asking you to only show one or two columns of data, select all the columns if you have fewer that 15 columns. (This will work for our our project because there are not too many total columns.) We can use the Chart Wizard to deselect the columns we don't want to show.

You can also hide columns to aid in the selection process, but if you unhide the data, the chart will add the data that you hid.

If you have data that has very many columns (20-200 or more) and you just need to select a few, it may be best to select non-adjacent data manually.

To select multiple non-adjacent cells:

Mac: select first selection then press the commmand key to add another selection. Continue as needed.
PC: select the first selection then press the Ctrl key to add another selection. Continue as needed.

In my expereince, it is easier to teach the concepts by selecting all the columns and use the the Chart Wizard for charts with 10-15 columns of data. So that is the method I like to demonstrate first. I will also demonstrate hiding columns and selecting non-adjacent columns.

Here is a link from Microsoft which goes into greater depth on even more ways to select data for charts. (This is a Mac help page, but it is applicable to PC. AS noted above, the only difference is that the shortcut key for selecting non-adjacent data is on the PC is the Control Ctrl key instead of the commmand key.

Using the the Chart Wizard to Create a Chart#

If you select correctly, then the Chart Wizard will make the rest of the tasks easy. We will create two charts now.

Link to Specific Steps for Required Tasks

We will use the Spring 2021 lecture page link from last semester: Value to Categories

18-Chart 1 Data: Compare Value to Categories#

Learning objectives: Duplicate Worksheet, Select/Sort Data, Create Chart, remove unneeded data, format chart#

There are a lot of steps in creating a chart in Excel, but once you get the idea of how and why, it only takes a short time to make a chart and is very, very easy.

Prepare the Data for the Chart 1-5.#

Numbers of Persons Serving in the Korean War is fine for the chart title on the resultant chart, but it will not fit on the worksheet tab name at the bottom of the workspace, so name it Korean War Chart on the tab.

Copy a sheet rename it by right clicking on the Worksheet and choose copy.
Move the new sheet to the end of the worksheets.
Identify the label data.
Identify the numerical data.
After the data is identified, Sort or Filter ALL the data. (This is a preview of the data tools session for next week.) You must make sure you sort all the related data.

Select the Data for the Chart, Create the Chart, Move the Chart to a New Worksheet#

Select the cells you need. You will have to limit what you select so that only the data you need is shown in the chart. There are multiple ways to limit what you select.

The most powerful way to do this is counter intuitive: you select more than you need and then you turn off the data columns you don't want to show using chart tools via a right click.

Use the Chart wizard to easily make a chart.
Right click on a blank part of the chart to get a popup window to move the chart to a new sheet.
Name the sheet as directed. Name the tab with the Shorter Name.
Format the chart to personalize the colors, type etc.

Link to Very Specific Steps for Required Tasks

Value Percentages to Categories

Below is a general overview of what we are doing.

19-Chart 2 Data: Compare Value Percentage to Categories#

We will repeat all of the same steps we did in Chart One, but we will have two columns of data instead of one, so we will have to create different type of chart, a 100% Stacked Chart. we don't have to do a sort or a filter.

After we get the chart on a new sheet, we will format it and include a free-floating object.