Free Workbook to Record Actuals and Budget, and Compare Actuals vs Budget

In honor of Spreadsheet Day, 2011, I’ve prepared a workbook where a person with minimal knowledge of Excel can enter amounts they actually earned and spent, enter a budget for a month, and have Excel automatically show them a comparison of actual amounts versus budget amounts as well as show actual trends. Please see the article written by Debra Dalgleish of Contextures at both Excel Student Budget: Spreadsheet Day 2011 and in her Pivot-Table blog. (From my perspective, Debra has the best Excel website on the planet!) Please post any questions you may have here and I’ll answer them promptly. I’d also love to hear any suggestions you have to make it better.

Click this link to download a free version of the Actuals and Budget Excel 2007 workbook (zipped, no macros).

If you’ve never prepared a budget before, just record your actual amounts for three or four months, then look for trends in the PivotTable in the sheet named “Actuals by Month.” Hopefully, you’ll then be better able to prepare a budget.

Posted in Excel PivotTables | 3 Comments

Check out the 2011 Challenge: 30 Excel Functions in 30 Days at Contextures.com

Want to quickly learn more about Excel from one of the best? If so, please be sure to check out the 2011 Challenge: 30 Excel Functions in 30 Days being presented by Debra Dalgleish. Debra is the author of Contextures.com, the best Excel site on the web! Don’t delay… It starts January 2nd at her blog site.

Posted in Uncategorized | 1 Comment

Copying (Not) With Filters On

Excel’s filters offer an extremely powerful way to analyze information, but I avoid copying when filters are activated. I’ll explain why in this article, and show you another, safer technique to use.

Click this link to open an Excel 2007 file (zipped, no macros) that we’ll use to discuss this issue. Note that the letter “C” appears three times in Column A, as shown here:
CopyingNOTWithFiltersOn_05b_Start

Let’s assume we want Column B to contain just the “C’s” from Column A, and we decide to do this by copying.

  • Filter the first column to show only C’s. The worksheet should look as shown here:
    CopyingNOTWithFiltersOn_10b_Filtered
  • Select the three cells containing the letter “C” and copy to Cell B2. The worksheet should look as shown here:
    CopyingNOTWithFiltersOn_15b_After_Paste

Did you notice what happened. Where are the other two “C’s” that we pasted? Let’s find out.

  • Clear the filters from Column A. The worksheet should now look as shown here:
    CopyingNOTWithFiltersOn_20b_AfterPaste_FiltersOff

Do you see how Excel did not paste the cells where we wanted? Further, can you imagine the damage that would occur if there were other values in Column B that we overwrote? This is the reason I avoid copying when Filters are being used.

So, is there another, safer way to accomplish this task? Absolutely.

  • Click the Undo icon multiple times until the file is back to it’s original appearance.
  • Filter the first column to show only “C’s” as we did earlier.
  • In Cell B2, enter the formula “=A2” and press the Enter key.
  • Click Cell B2 again.
  • While holding the Shift key down, press the Down Arrow key twice. The worksheet should now look as shown here:
    CopyingNOTWithFiltersOn_25b_AfterHighlightingCells
  • In the Home tab, in the Editing group, click the Fill icon CopyingNOTWithFiltersOn_30a_FillIcon.
  • In the list that appears, click Down.
  • The worksheet should look as shown here:
    CopyingNOTWithFiltersOn_35a_AfterFillDown
  • Clear the filters from Column A again. The worksheet should look as shown here:
    CopyingNOTWithFiltersOn_40a_AfterClearFilters
  • Note that Column B now has the correct values from Column A.

For the reason shown in this article, I make it a practice to never copy when filters are on.

For information on how to use Filters, including multiple Filters and custom Filters, please see Chapter 6, “Using Filters to Analyze Data” in the book “Simply Learning Excel 2007” available at Amazon.com.

Posted in Excel Filters | Leave a comment

How to Avoid Disaster When Sorting

Sorting is another powerful feature of Excel, but if we’re not careful, we can create a problem from which only Undo can rescue us, and if we can’t Undo, we’re sunk. Let me explain.

Click this link to open an Excel 2007 file (zipped, no macros) that contains a list of some of the largest cities in the world, along with their associated country, in no particular order. Here’s what the file looks like before we sort the data:
AvoidSortingProblems_05b_B4_Sorting

Let’s see what happens when we sort the data by City (Hint: This is the incorrect way):

  • Click Cell A1.
  • In the Home tab, in the Editing group, click the Sort & Filter icon.
  • In the menu that appears, click the item labeled Sort A to Z.

Here’s what the file should look like:
AvoidSortingProblems_10b_After_Incorrect_Sorting

Did you notice what happened? The cities were sorted, but the associated countries were not. Worse, there is no systematic way of getting the cities back to their original order, other than Undo, because the cities were not originally sorted in any particular order. Why did this happen? Because the hidden Column B was blank, so Excel assumed we only wanted to sort the data in Column A.

Can you imagine if this happened, and we couldn’t Undo it, especially if we were dealing with large amounts of data? Let’s Undo this, and use an approach that will avoid the problem we just experienced.

  • In the Quick Access Toolbar, click the Undo icon.
  • Click, hold and drag Rows 1 through 12.
  • In the Home tab, in the Editing group, click the Sort & Filter icon.
  • In the menu that appears, click the item labeled Sort A to Z.
  • Click Cell A1 to remove the highlights from the worksheet.

Here’s what the file should look like:

AvoidSortingProblems_15b_After_Correct_Sorting

Note that the sort now worked properly, with the city properly being sorted along with it’s associated country.

For a more complete discussion of how to safely sort data, please see Chapter 5, “Sorting Data, Wrapping Text” in the book “Simply Learning Excel 2007” available at Amazon.com.

I hope this topic will prevent you from experiencing the problem with sorting we talked about. In two weeks, we’ll talk about how to avoid problems when copying data with filters on. See you then!

Posted in Excel Sorting | Leave a comment

Using Default Worksheets

I ended last week’s blog introducing the concept of a default worksheet that we could insert at any time in any workbook to help make copying page formats even faster and easier.  In today’s blog, we’ll actually save a default worksheet, and see how it works.

  • Click here to open an Excel 2007 workbook (zipped, no macros) that already has the Page Layout we want to use as a default worksheet. If you want to learn more about setting up customized Page Layouts, please see Chapter 3 “Printing the Worksheet” in the book “Simply Learning Excel 2007” available at Amazon.com.
  • Print Preview the worksheet (Office Button > Print > Print Preview). Note the customized page layout – gridlines, page numbers, file name, special sheet name, etc. You may be asking yourself why I didn’t leave the worksheet blank. The answer is that we will remove all content before we save it as a default worksheet, but Print Preview will only work if the worksheet has some content.
  • Right-click Row 10 and in the menu that appears, click Delete.
  • Click Cell A1.
  • Click the Office Button.
  • In the menu that appears, place the mouse pointer over the words Save As, and in the menu that appears on the right, click Other Formats.
  • In the box labeled Save As that appears, click the bar just to the right of the words “Save as type:” and in the list that appears, click “Excel Template (*.xltx)” (your computer may not show the file extension) as shown here:
    image
  • In the white box to the right of the words “File name:,” type the word Sheet.
  • The bottom of the box labeled Save As should now look as shown here: (Your computer may not show the file extension.)
    image
  • In the file location at the top of the box labeled Save As, specify the XLSTART folder. The location of that folder will vary depending on what operating system you’re using. To find out the exact location for your operating system, I recommend typing xlstart in a search engine such as Google, or in the Excel Help.
  • Click Save and close the file.

Let’s use the default worksheet we just saved to copy formats. For a more thorough discussion on copying page layouts, please see last week’s blog.

  • Click here to open an Excel 2007 file (zipped, no macros) we can use to copy formats.
  • If not already selected, click the sheet named Sheet2.
  • Print Preview Sheet2 and note that it has no custom page formatting.
  • Right-click the Sheet2 tab and in the menu that appears, click Insert.
  • In the box that appears labeled Insert, click the Worksheet icon, then click OK. Note that a sheet named Custom Sheet is now to the left of the sheet named Sheet2 and is the active sheet.
  • While holding down the Ctrl key, click the sheet tab labeled Sheet2.
  • Release the Ctrl key.
  • 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-d[2]
  • Click OK. That one little step has now copied the page layout of the default worksheet we just inserted to Sheet2.
  • Right-click either of the two highlighted sheet tabs (Custom Sheet or Sheet2) and in the menu that appears, click Ungroup Sheets.
  • Print Preview Sheet2 again, and note that it now has the custom page layout we established for the default worksheet.
  • Close the Print Preview.
  • Right-click the sheet named Custom Sheet and in the menu that appears, click Delete.

Excel also allows us to establish default workbooks using a similar process. A default workbook will open up automatically every time we open Excel. To find out more, search for the words “Customize how Excel starts” in Excel help.

I hope this blog has been helpful to you. In two weeks, we’ll talk about how to avoid problems when sorting data. See you then!

Posted in Default Worksheet, Excel Copy Formats | 1 Comment

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