Jan's Working with Numbers

Step-by-Step: Format Column Chart

Start with: trips14-Lastname-Firstname.xlsx (saved in previous lesson)

Change Chart Type

1. Open trips14-Lastname-Firstname.xlsx from your Class disk in the excel project3 folder to Sheet2, if it is not still open.
2. Save As trips15-Lastname-Firstname.xlsx in the excel project3 folder on your Class disk.
   
3. Right click in a blank area of the chart and choose  Change Chart Type…  from the context menu.
   The Chart Type dialog opens.
   

4. Select Column, which is Excel's default chart type, and choose the first sub-type, Clustered Column.

   

5. Click OK.
   

   The chart is redrawn as a set of columns, using the default settings for this chart type.
   The changes you made are kept, like the title text, the added text box 'Week', and the data point color.

   

   Data Point Labels: The data points are now labeled with the values they represent instead of the percentages that were in the pie chart.

   Colors: What was the 19% wedge for Week 2 is now the purple column, labeled 23. It retained the color you assigned to its pie wedge. All the other wedges are converted to the default color, which is different in Excel 2013 and in Excel 2016.

   The legend does not accomplish much since there is only one series of columns.  You will get rid of it shortly.

   You colored Week 2 differently because it had the highest number of tickets sold. That works well in a column chart, too.

6. Save.
   [trips15-Lastname-Firstname.xlsx]
   

Chart Area: Add Texture Background

The solid white background is good for many charts, but sometimes a gradient color, pattern, or image can make your chart more attractive. You do have to be careful that the chart remains easy to read.

1. Right click in a blank area around the chart and choose  Format Chart Area…  from the context menu.
   The Format Chart Area dialog or pane opens to its first page, Fill, with the Automatic radio button selected.

   Problem: No 'Format Chart Area' command
   You clicked on a chart part or in the Plot Area instead of a blank area around the chart.
   Solution: Right click beside the title text box but not in that text box.
   

2. Experiment: Fill
   Try out other choices on the Fill page for fills. Live Preview works once you have made a selection.

   Try different solid colors, gradients, pictures, textures, and patterns.
   You can even use your own pictures as a background.

   When you are ready to continue...

3. Click on the radio button Picture or texture fill.
   More choices appear below the radio buttons.
4. Click on the Texture button to open its gallery.

5. Click on Blue tissue paper on the 4th row down.
   For once the choices are the same in Excel 2007, 2010, 2013, and 2016.

   
   

6. Click on OK to close the dialog.

   Your chart now shows a repeating blue textured image as a background. Much prettier than a plain white background! Of course a busy or dark image could make it hard to read your chart.

   

   In Excel 2007 and 2010 the Plot Area still has a white background. But Excel 2013 and 2016 change the Plot Area background to None when you apply a Chart Area fill. This lets the fill show through the Plot Area, too. Do you like this effect? Does it make the chart harder to read?

7. Excel 2013, 2016: Fix Plot Area fill

   • Click in the Plot Area (where the columns are) but not on a column or a grid line.
     The formatting pane changes to Format Plot Area.

   • Click on Solid fill.

     If necessary, change the Color to White.
     The chart gets its white background back for the Plot Area.

     

8. Save.
   [trips15-Lastname-Firstname.xlsx]
    

Remove Chart Part: Legend

There are two ways to remove a chart part - Select and press the DELETE key or use a ribbon menu of formatting options and choose None.

Excel 2007, 2010: The Chart Tools: Layout tab has buttons which open a menu of choices for formatting various parts of a chart. 'None' is the first choice on all of these buttons.

Excel 2013, 2016: The ribbon tab Chart Tools: Design has a button Add Chart Element that opens a cascading menu of chart parts. Each chart element includes the choice 'None'.

1. If necessary, select the chart again.

2. Experiment: Legend

   • View the choices for formatting the Legend:
     Excel 2007, 2010: On the Chart Tools: Layout ribbon tab in the Labels tab group, click on the button Legend.
     A list of positions for the legend appears.
     
     Excel 2013, 2016: On the ribbon tab Chart Tools: Design click the button Add Chart Element and hover over Legend to see the submenu.
     
     The icons show a sample of the legend's position.
   • Try out each one.
   • Click on More Legend Options... to open the Format Legend dialog.
   • Try out various combinations of Fill, Borders, Effects, etc.

   The current chart is not really helped by a legend at all since there is only one data series.

   When you are ready to continue...

3. Open the menu of formatting choices again for Legend and click on None.
   The legend vanishes, but the text box Week that you added to label it remains.
   
   Alternate method: Click the legend on the chart to select it and press DELETE.
    
4. Click on the text box containing Week to select it.  
5. Press the DELETE key.
   The text box is gone and the plot area expands to take up the space.
   
6. Save.
   [trips15-Lastname-Firstname.xlsx] 
7. Experiment: Other chart parts
   • Try out choices for formatting chart elements from the ribbon, like Labels, Axes, and Background. You can hide or reveal any of these parts of a chart. You can format many features, including background and lines.
   • Use Undo to return your chart to its previous state or close without saving and reopen the document.

Edit Chart Title

You can edit the title and axis labels on a chart directly on the chart or in the Formula bar. Your typing will replace all of the text unless you select part of it first.

1. Click on title of the chart, 'Number of people'.
   You want the text box selected, not the text. That way your typing will replace this text.

   The Formula bar is blank!

2. Type  Number of Tickets Sold, which will show in the Formula Bar but the chart title does not change yet.
   

3. Press the ENTER key or click out of the chart.
   Your typing will replace the existing text. The new text shows in the Formula Bar only at first. It shows in the text box after you press ENTER or click out.

   This text explains the chart better and has more appropriate capitalization.

   Alternate method: Select the title's text box and then drag inside the text box to select the text. Your typing shows directly in the box.
   This is a good method for editing text in place. If you have trouble with the selecting, you can edit in the Formula Bar.
   

4. Save.
   [trips15-Lastname-Firstname.xlsx]

Show Axis Titles

There are many parts that can be on a chart, but they may not be on the layout you picked to start with. The ribbon makes it easy to add the ones you need.

Edit Axis Titles 

1. Since the title for the vertical axis, Y-axis, is already selected, type Tickets Sold and press ENTER.
   Your typing shows in the Formula Bar until you press ENTER. Then it replaces 'Axis Title'.
2. Click the box for the title of the horizontal axis (X-axis) and type  Week of Special Offer 
3. Click out of the text box to enter your change.
   The font size seems a bit small compared to the title and the size of the columns.
4. Click on the vertical axis title.
   
5. On the Home tab, click on the Increase Font Size button twice.
   The plot area resizes to be narrower as the title takes up more space.
6. Click on the horizontal axis title and do the same.
   Again the plot area resizes.
7.  Save.
   [trips15-Lastname-Firstname.xlsx]
   

Edit Sheet Tab and Print

1. Change the name of the sheet from 'Pie Chart' to  Tickets Sold Chart 
   Hint: Right click on sheet tab and choose Rename or double-click the tab and type.)
2. Deselect the chart by clicking off the chart.

3. Open Print Preview.
   The preview is not in gray scale, even if the printer is set to print that way.  This makes it hard to be sure what the printed page will look like.

   

4. Save.
   [trips15-Lastname-Firstname.xlsx]
5. Print the sheet.
   Printing in color is certainly prettier for this one.