Learning Objectives

In this section, we will take a look at each of the worksheets created in the previous sections. Since these worksheets contain a combination of data and charts, there are specific things to watch for if you will be printing the sheets.

We will start by looking at each worksheet in Print Preview in Backstage View.  We will then make any changes necessary, such as changing the orientation and scaling or moving charts around on the worksheet.  To make sure we don’t miss any worksheets, we are going to review the worksheets in the order they appear in the tabs.

Previewing Chart Sheets For Printing

Data file: Continue with CH4 Charting.

The All Excel Classes is a chart sheet. This means that it does not contain any data; remember that chart sheets just contain charts.  We still need to review it in Print Preview.

1. Click on the All Excel Classes worksheet tab.
2. Go to Print Preview by clicking Print in Backstage View.
3. Notice that the chart will print on the entire page, in Landscape orientation.
4. There is nothing to change. Exit Backstage View.

Printing Worksheets With Data And Charts

The Stock Trend worksheet has a lot of data and multiple embedded charts.  We need to print the data and the charts, which will require modifications to the page setup.

 1.  Click on the Stock Trend worksheet tab.

 2.  Go to Print Preview by clicking Print in Backstage View.

 Mac Users choose “File/Print…” from the Excel File menu option.

3.  Notice that this worksheet is currently printing on seven pages.

4.  As you click through each page you should make the following observations:

• The data is split between the first and third pages.
• The line chart starts on the first page, but part of it is also on the second page.
• The double-line chart starts on the third page and then finishes on the fifth page.
• The fourth and sixth pages are blank.
• The last page (page 7) has a column of seemingly random numbers.

 5.  Exit Backstage View.

6.  The first thing we are going to do is hide the numbers that are appearing on page 7.  We are going to hide the column, instead of deleting the numbers, in case the numbers are being utilized somewhere else in the workbook.

7.  Scroll to the right on the worksheet until you find the numbers in column AH.

8.  Click anywhere in column AH.

9.  On the Home ribbon, click the Format button in the Cells group.

10.  In the Visibility section, select Hide & Unhide then select Hide Columns.

Figure 4.29 Hide Columns in Format Menu

11.  The visible column headings should now go from AG to AI.

12.  Return to Print Preview in Backstage View to see the changes to the printed worksheet.

13.  Notice that there are now five pages.  The data and charts are still splitting across multiple pages, but the numbers in column AH are no longer going to print.

14.  Remain in Backstage View for the next steps.

The data is still split between pages 2 and 3, and the charts are splitting oddly as well.  The first step we will try to fix these issues is to change the page orientation and scaling.

 1.  While still in Backstage View, change the page orientation to Landscape (use the Orientation drop-down menu in the Settings section).

 Mac Users click the Landscape Orientation button 

2.  This puts all of the data on one sheet, but the charts are still split between multiple pages.

3.  Change the page scaling to Fit Sheet on One Page (use the Scaling drop-down menu in the Settings section).

 Mac Users click the Scale to Fit option 

4.  This fits everything on one page, but it is too small to be able to read.

5.  Change the page scaling back to No Scaling.

 Mac Users: uncheck the Scale to Fit option

The next thing we will try is moving one, or both, of the charts. In order to move the charts, we need to exit out of Backstage View.

 1.  Exit Backstage View.

 2.  Switch to the View ribbon and then select Page Break Preview.  Your screen should look similar to Figure 4.30.  (Remember that the dotted blue lines indicate automatic page breaks.)

 3.  Move the 24 Month Comparison (double-line) chart closer to the top of its page.

 4.  Move the May 2014-2015 Trend for NASDAQ Sales Volume (line chart) so that it is under the 24 Month Comparison chart.

 5.  The link to the data source is still at the bottom of page 2 (in A50:A51) so you need to move it as well.  Using your preferred method, move the text from A50:A51 to M31:M32.

Now your screen should look similar to Figure 4.30.

We don’t want the data source link text to print on its own page, but there is no room to move it onto the same page as the charts. To fix this, we are going to remove the automatic page break between the charts and the text in M31:M32.

 1.  Place your pointer on the horizontal blue dashed line (automatic page break) between the line chart and the Data Source link text.

2.  When your pointer changes to the double arrow (pointing up and down), drag the page break down into the gray area. This removes the page break.

3.  If your vertical automatic page break between columns K and L moves, drag it back between columns K and L. This will make it a solid blue line, which will no longer adjust automatically.

Note: you may need to slightly re-size the two charts in order to make your screen look like Figure 4.31. Your “goal” is to only have two pages.

Now you need to do one final check of this worksheet in Print Preview.

 1.  Go to Print Preview and look at both pages. Page 1 should contain just the data and page 2 should have both charts and the Data Source link text.

2.  Exit Backstage View and save the file.

Preview Remaining Worksheets For Printing

The remaining worksheets need to be reviewed. Some of them will need minor changes and some will not need any changes.  You will need to preview each one and then make the specified changes.  In the following steps, you will preview and modify all other worksheets.

1.  Grade Distribution,  Enrollment Statistics, and Admissions sheets – the charts split across two pages. Fix this by changing the orientation (Landscape) and scaling (Fit Sheet on One Page).

2.  The remaining chart sheets should not need any changes.

Printing A Chart Only

Sometimes you might have a worksheet that has data and a chart, but you only want the chart to print.  That is the case with the Enrollment Statistics worksheet.

 1.  Switch to the Enrollment Statistics worksheet.

2.  Select the Gender Comparision chart.

 Mac Users: Steps 3-5 will not work in Excel for Mac. See alternate steps below step 5.

3.  Go to Print Preview.  Only the chart is printing.  (If it shows the data printing along with the chart, exit Backstage View and be sure to select just the chart on the worksheet.)

4.  If needed, change the orientation to Landscape.  This orientation looks better when printing just a chart.

5.  Exit Backstage View.

 Mac Users: the only way to print a Chart separately is to click on the chart you want to print move it to a new sheet by clicking on the chart, click the Move Chart button on the Chart Design tab, click New Sheet then choose File/Print from the Excel menu and switch to Landscape Orientation if necessary.

Hiding A Worksheet

You have actually decided that you do not want the Expenses sheet to be visible at all, but you do not want to delete it. We are going to hide it from anyone looking at the workbook.

 1.  Right-click on the Expenses tab.

 Mac Users should hold down the CTRL key and click on the Expenses tab

2.  Select Hide from the menu that appears.  The sheet should no longer be visible.

3.  Save the CH4 Charting workbook.

4.  Submit all three files from this chapter:  CH4 Charting.xlsx, CH4 CC Enrollment.docx, and CH4 PowerPoint CC Enrollment.pptx as directed by your instructor.