Copying Formats, Part 5: Page Layouts

Excel certainly has lots of options for formatting a page to be printed, but do we have to apply each of those features every time we print, especially if we want to print out a page in a way that’s similar to another worksheet we just printed? Thankfully, the answer is no, and showing  how to copy page layouts is the subject of this week’s blog. Since setting up page layouts, including Print Preview, is covered in detail in Chapter 3 “Printing the Worksheet” in the book “Simply Learning Excel 2007” available at Amazon.com, we won’t cover that in this article.

Here are the steps we’re going to follow to copy page layouts:

  • Click here to open an Excel 2007 file (zipped, no macros) named “Checkbook-PageLayout-BEFORE.xlsx.” Note that Sheet2 is to the left of the sheet named Checkbook, and Sheet1 is to the right.
  • If not already selected, click the sheet named Checkbook. This is the sheet containing the page layout that we’d like to copy to the other two sheets in the file.
  • While holding down the Ctrl key, click the sheet named Sheet1 then the sheet named Sheet2.
  • Release the Ctrl key.
  • All three sheet names should now be highlighted, as shown here:
    Copy Formats-Part 5-02c-All three sheets selected
  • Print Preview the three selected sheets (Office Button > Print > Print Preview). Use the PgDn and PgUp keys on the keyboard or the mouse wheel to view each page. Note that the first and third pages have a blank page layout, while the Checkbook sheet has an extensive, customized page layout – landscape orientation, header, footer, etc.
  • Use the PgDn and PgUp keys on the keyboard or the mouse wheel so that the sheet named Checkbook is in the Print Preview window.
  • Close the Print Preview.
  • Click the Page Layout tab in the Ribbon.
  • In the Page Layout tab, in the Page Setup group, click the little Page Setup dropdown arrow, as shown here:
    Copy Formats-Part 5-05b-Page Setup dropdown arrow
  • Click OK. That one little step has now copied the page layout of the Checkbook sheet to the other two sheets.
  • Let’s verify this by Print Previewing all three pages (Office Button > Print > Print Preview). Notice that all three pages now have the same, extensively customized, page layout that only the Checkbook sheet had before we started.
  • Close the Print Preview.
  • We have one more, critical step. Right-click one of the three selected sheet names at the bottom of the window.
  • In the menu that appears, click Ungroup Sheets. If we didn’t do this, then whatever we entered in any cell in one sheet would automatically appear in the same cell in both of the other sheets that were selected. Uugghh. (Can you tell that’s happened to me before?)

Click here to open an Excel 2007 file (zipped, no macros) named “Checkbook-PageLayout-AFTER.xlsx” and compare it to your results.

While what we covered today should help make setting up Page Layouts easier and faster, we still need to have a sheet in our workbook that has a page layout we want in order to copy it to the remaining sheets. But what if the workbook we’re working with doesn’t have such a sheet? One workaround is to copy a sheet that has the page layout we want from another workbook, and follow the steps above. Another approach – and the one I use – is to set up a default worksheet that we can insert at any time in any workbook. That’s what we’ll talk about next week. Until then, I hope you all have an enjoyable week.

Posted in Excel Copy Formats | Tagged , , , | Leave a comment

Copying Formats, Part 4: Charts – PivotCharts

In last week’s blog, we demonstrated copying chart formats between regular charts. But, what about copying chart formats between PivotCharts? And, what about copying chart formats between regular charts and PivotCharts? These two questions are the subject of this week’s blog.

We’re going to use the same file we started with last week, but it is below this week’s blog and last week’s blog. The file contains temperature data from weather.com for Philadelphia, Pennsylvania and Myrtle Beach, South Carolinas and is charted using variously formatted regular charts and PivotCharts. To learn how to prepare regular charts and PivotCharts, please see Chapter 8, “Using Charts to Present Data” in the book “Simply Learning Excel 2007” available at Amazon.com.

We’ll start by copying chart formats between PivotCharts. Please click this link to open an Excel 2007 file (zipped, no macros) named “Temperature-Comparison-BEFORE.xlsx.” If not already selected, click the sheet named “Pivot-Tables & Charts.”

  1. In the chart entitled “Average Temperatures,” let’s click somewhere in the white space within the chart. If you’re not sure what I mean, try clicking in the white space to the left of the words “Average Temperatures” as shown here:
    Copy Formats-Part 4-05c-click white area of chart 
  2. In the Home tab, in the Clipboard group, click the Copy icon.
  3. In the chart entitled “Average High Temperatures,” click somewhere in the white space within the chart. If you’re not sure what I mean, try clicking in the white space to the right of the words “Average High Temperatures.”
  4. In the Home tab, in the Clipboard group, click the Paste icon.
  5. That’s it! The formats of the top two charts in this sheet should now be identical

Now, we’ll apply that same format to the chart at the bottom of this sheet. Note that we don’t need to copy the format of the first chart again.

  1. In the chart entitled “Average Low Temperatures,” click somewhere in the white space within the chart.”
  2. In the Home tab, in the Clipboard group, click the dropdown box under the Paste icon.
  3. In the menu that appears, click the Paste icon.
  4. The formats of all three charts in this sheet should now be identical.

Copying formats between regular charts and PivotCharts is not as straightforward as perhaps it could be, so here are some guidelines:

  • To copy the format of a regular chart to another chart, use Copy and Paste Special > Formats.
  • To copy the format of a regular chart to a PivotChart, use Copy and Paste (not Paste Special).
  • To copy the format of a PivotChart to another PivotChart, use Copy and Paste.
  • To copy the format of a PivotChart to a regular chart… DON’T. Despite using Copy and Paste Special > Formats, Excel will copy the data series as well as the formats. Worse, using the Undo feature will only undo the format change, and change the data series to a formula with fixed data points.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Temperature-Comparison-AFTER.xlsx” and compare it to your results.

Next week, we’ll talk about copying page layouts. Until then, I hope you all have an enjoyable week.

Posted in Excel Copy Formats | Tagged , , , | 1 Comment

Copying Formats, Part 3: Charts – Regular Charts

Have you ever had a situation where you needed to change the format of a bunch of charts? Turns out Excel makes that an easy task by allowing us to copy only a chart’s format and apply it to other charts. To demonstrate this, I’ve assembled temperature data for Philadelphia, Pennsylvania and Myrtle Beach, South Carolina… where I am, and where I’m considering moving to eventually. In order to stay focused on the topic of copying formats, I’ve already prepared regular charts and PivotCharts comparing average temperatures, average high temperatures, and average low temperatures, but I’ve used different chart formats. In this session we’re going to make all three regular charts look the same. Next week, we’ll focus on PivotCharts. To learn how to prepare regular charts and PivotCharts, please see Chapter 8, “Using Charts to Present Data” in the book “Simply Learning Excel 2007” available at Amazon.com.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Temperature-Comparison-BEFORE.xlsx.” If not already selected, click the sheet named “Charts-Regular.” We’ll first copy the format of the chart at the top to the chart just underneath it.

  1. In the chart entitled “Average Temperatures,” let’s click somewhere in the white space within the chart. If you’re not sure what I mean, try clicking in the white space to the left of the words “Average Temperatures” as shown here:
    Copy Formats-Part 3-05c-click white area of chart
  2. In the Home tab, in the Clipboard group, click the Copy icon.
  3. In the chart entitled “Average High Temperatures,” click somewhere in the white space within the chart. If you’re not sure what I mean, try clicking in the white space to the right of the words “Average High Temperatures.”
  4. In the Home tab, in the Clipboard group, click the dropdown box under the Paste icon.
  5. In the menu that appears, click Paste Special.
  6. In the box that appears labeled Paste Special, click the button to the left of the word Formats, as shown here:
     Copy Formats-Part 3-10-Paste Special box
  7. Click OK.
  8. The formats of the top two charts in this sheet should now be identical.

Now, we’ll apply that format to the chart at the bottom of this sheet. Note that we don’t need to copy the format of the first chart again.

  1. In the chart entitled “Average Low Temperatures,” click somewhere in the white space within the chart.”
  2. In the Home tab, in the Clipboard group, click the dropdown box under the Paste icon.
  3. In the menu that appears, click Paste Special.
  4. In the box that appears labeled Paste Special, click the button to the left of the word Formats.
  5. Click OK.
  6. The formats of all three charts in this sheet should now be identical.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Temperature-Comparison-AFTER.xlsx” and compare it to your results.

Next week, we’ll demonstrate copying chart formats between PivotCharts. Until then, I hope you all have an enjoyable week.

Posted in Excel Copy Formats | Tagged , , , | 4 Comments

Copying Formats, Part 2: Cells & Columns Using the Format Painter

Similar to last week’s blog, we’re going to make the format of particular cells or columns identical to another cell or column, and, again, we’ll use a spreadsheet showing how to analyze refinancing options to demonstrate this feature. (This file also includes a break-even analysis worksheet from the Federal Reserve.)

We can copy formats using either the Paste Special feature, or the Format Painter icon. This week, we’ll use the Format Painter icon.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Refinancing-Analysis-BEFORE.xlsx.” Note that the cells in the top portion of Columns E thru H are unformatted, as shown here:
Copy Formats-Part 2-02b-BEFORE 
 

  1. Click Cell D1 thru Cell D7. In the Home tab, in the Clipboard group, double-click the Format Painter icon, as shown here:
     Copy Formats-Part 2-05b--format painter icon
  2. Double-clicking the Format Painter icon will allow us to paste formats multiple times. Place the cursor over Cell F1, but do not click. The top portion of the spreadsheet should look as shown just below. The active format painter is circled.
    Copy Formats-Part 2-10b-after double-clicking
  3. Click Cell F1, and note that the formats in Cells F1 thru F7 are now similar to Cells D1 thru D7.
  4. Click Cell H1, and note that the formats in Cells F1 thru F7 as well as Cells H1 thru H7 are now similar to Cells D1 thru D7, as shown here:
    Copy Formats-Part 2-15b-after applying twice 
  5. Did you notice that the format painter is still active? If we clicked some other cell, the formats from Cells D1 thru D7 would be applied again. To deactivate the Format Painter, press the Esc key on the keyboard. The Esc key is located in the upper left corner of most keyboards.

We’ll now use the same method to copy the formats from Column C to Columns E and G. Please use the screenshots above as needed.

  1. Click Column C. In the Home tab, in the Clipboard group, click the Format Painter icon.
  2. Click Column E. Note that the format from Column C has been applied to Column E.
  3. Click Column G. Note that the formats of both Column E and Column G are now similar to Column C.
  4. Press the Esc key to deactivate the Format Painter.
  5. Click Cell A1 to remove the highlighting in Column G.

Please click this link to open the Excel 2007 file (zipped, no macros) named “Refinancing-Analysis-AFTER.xlsx” and compare it to your file.

A final note: We could have clicked the Format Painter icon once, but that would have limited us to apply formats just once. My personal preference is to have it available to apply formats multiple times.

I hope this has been helpful for you. Next week we’ll cover copying chart formats. Until then, I hope you all have a wonderful week.

Posted in Excel Copy Formats | Tagged , , , , | Leave a comment

Copying Formats, Part 1: Cells & Columns Using Paste Special

Have you ever had a situation where you wanted to make the format of particular cells or columns identical to another cell or column? I encounter this situation somewhat frequently, and when I do, I use Excel’s format copying feature. With interest rates being as low as they are, I thought we’d use a spreadsheet showing how to analyze refinancing options to demonstrate this feature. (This file also includes a break-even analysis worksheet from the Federal Reserve.)

We can copy formats using either the Paste Special feature, or the Format Painter icon. This week, we’ll use the Paste Special feature. Next week, we’ll use the Format Painter icon to copy formats.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Refinancing-Analysis-BEFORE.xlsx.” Note that the cells in the top portion of Columns E thru H are unformatted, as shown here:
Copy Formats-Part 1-02b-BEFORE

  1. Click Cell D1 thru Cell D7. In the Home tab, in the Clipboard group, click the Copy icon, as shown here:
    Copy Formats-Part 1-05b--thecopy icon
  2. Click Cell F1. In the Home tab, in the Clipboard group, click the dropdown arrow below the Paste icon, and click Paste Special, as shown here:
    Copy Formats-Part 1-10b-Paste Special
  3. In the box that appears labeled “Paste Special,” click the radio button to the left of the word “Formats,” as shown here:
    Copy Formats-Part 1-15-Paste Special box
  4. Click OK. 
  5. Click Cell H1 and repeat the steps we just followed for Cell F1. Note that we didn’t have to copy Cells D1 thru Cell D7 again. When finished, press the Esc key located in the upper left corner of most keyboards to remove the blinking lines around Cells D1 thru D7.

We’ll now use the same method to copy the formats from Column C to Columns E and G. Please use the screenshots above as needed.

  1. Click Column C. In the Home tab, in the Clipboard group, click the Copy icon.
  2. Click Column E. In the Home tab, in the Clipboard group, click the dropdown arrow below the Paste icon, and click Paste Special. In the box that appears labeled “Paste Special,” click the radio button to the left of the word “Formats” and click OK.
  3. Click Column G and repeat the steps we just followed for Column E. Note that we didn’t have to copy Column C again. When finished, press the Esc key to remove the blinking lines around Column C.
  4. Click Cell A1 to remove the highlighting in Column G.

Please click this link to open the Excel 2007 file (zipped, no macros) named “Refinancing-Analysis-AFTER.xlsx” and compare it to your file. Note that the cells in the top portion of the “AFTER” version are now formatted, as shown here:
Copy Formats-Part 1-20b-AFTER

I hope this has been helpful for you. Next week we’ll cover copying formats using the Format Painter icon. Until then, I hope you all have a wonderful week.

Posted in Excel Copy Formats | Tagged , , , | 5 Comments