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 | 4 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