David Ringstrom

Author's posts

Mar 23

Office 2007 Themes standardize document look, feel

By David H. Ringstrom, CPA


It can be a significant challenge to ensure that your company’s documents have the same look and feel, particularly when you’re using Excel, Word, PowerPoint, and Outlook together. Fortunately Office 2007’s Themes feature can simplify the process.
Office 2007 ships with twenty built-in themes that you can readily apply, or you can download dozens more for free from the Microsoft Web site. You can even create your own themes that use the exact colors of your company logo or other design elements. I’ll briefly explore each of these options in this article.
First, let’s see how to access themes in Excel 2007. As shown in Figure 1, choose Page Layout, and then click the Themes button to display a list of choices.


Figure 1: Office 2007 ships with 20 pre-built themes that you can apply to your documents.


To apply a theme, simply click on the name, or hover over a theme to see how the fonts, borders, and colors within your document will change. As shown in Figure 1, you can access more themes through the More Themes on Microsoft Office Online command. This command takes you to the Microsoft Web site, where you can browse dozens more themes that you can download to your computer at no charge.
You also can create new theme elements on your own. As shown in Figure 2, click the Colors button in the Themes section of Excel 2007, and then choose New Theme Colors. The dialog box shown in Figure 3 will appear, from which you can assign colors that suit your needs.


Figure 2: You can choose from existing color sets, or create a new set.


Figure 3: You can create your own set of theme colors in Office 2007.


The Fonts button in the Themes section allows you to manage fonts, while the Effects button allows you to choose from prebuilt text effects.
Theme functionality in Word 2007 closely mirrors that of Excel 2007, with one caveat. Excel 2007 allows you to apply themes to any sort of document that you have open in Excel, including Excel 97-2003 documents. Curiously, Word disables the Theme option when you save your document in the Word 97-2003 format. Also, Outlook 2007 offers limited support for themes, as you must use Word 2007 as your e-mail editor to apply themes to your e-mail.


As you might expect, themes work a little differently in PowerPoint. You access themes through the Design tab of the PowerPoint ribbon, but all other functionality works the same. If you create a theme in Excel, Word, or PowerPoint that you want to use in one of the other applications, simply choose Save Current Theme, as shown in Figure 4.


Figure 4: Themes saved in one Office 2007 application can be opened in others.
To open a saved theme, choose Browse for Themes from the Theme menu, as shown in Figure 4. Your theme will appear in a new Custom theme section.


If you need even more control with regard to customizing themes, try the free Theme Builder for Office 2007. Although the application is still in beta, it provides granular control over themes that goes far beyond what you can do in Word, Excel, or PowerPoint.
Visit the Theme Builder Home page to get the free download. Before you click the Download link on the left-hand side of the page, do note that you must first install two components:
Keep in mind that the Theme Builder requires you to make numerous decisions that you might not be prepared for, so make sure that you really want a deep level of control over your themes before you download and install the software. For most users, simple color and font changes through the Office 2007 applications likely will suffice.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 23

Understanding the Undo option in Excel

By David H. Ringstrom


One of my favorite features in Excel is the Undo feature, which as you might expect, allows you to undo the last action that you carried out in Excel. Indeed, you can generally undo multiple steps in Excel. However, there are some caveats to this functionality, as well as a couple tricks you may not know.


The keyboard shortcut that I use most often in Excel is Ctrl-Z, which is the shortcut for Undo. I can press it repeatedly to undo several actions. In Excel version 2003 or earlier, I can undo up to the last 16 steps that I’ve carried out. This list of 16 steps is known as the Undo Stack. However, when I’m using Excel 2007 or 2010, I have a much larger Undo Stack available to me: I can undo dozens of recent actions.


Excel 2003 and earlier versions also have another key restriction related to the Undo feature. When you save your workbook in these versions of Excel, the Undo Stack is erased, meaning you lose the ability to undo any actions you carried out prior to saving. Excel 2007 and 2010 don’t have this limitation, which means you can save your workbook, and then still undo previous actions.


Many users rely on the Undo button on the Excel 2003 toolbar, or the Excel 2007/2010 Quick Access toolbar. However, a lot of users don’t realize that the Undo button has a drop-down menu, as shown in Figure 1. When you click the menu, you can undo multiple actions at once by selecting a group of items from the list. You are, however, limited to choosing a consecutive list of items from the top down, and you can’t skip items in between. However, this also allows you to see exactly what actions will be undone.


Figure 1: You can select multiple steps to Undo.
Keep in mind that in all versions of Excel there are certain actions that will clear the Undo stack. As previously mentioned, saving a workbook in Excel 2003 and earlier will clear the Undo Stack. In any version of Excel, the Undo Stack will be erased if you delete a worksheet from a workbook, or run a macro. Always be sure to save your work before you carry out either of these actions if you want to preserve a fall-back position in case you encounter unexpected ramifications.


A sister function to Undo is Redo, which has a keyboard shortcut of Ctrl-Y. The Redo toolbar button has a drop-down menu just like Undo. In fact, you can undo, and then redo, one or more actions. When necessary, this allows you to roll back the spreadsheet to how it looked a few steps prior, and then roll it forward to your latest update.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jan 05

Trick For Hiding Rows

In Excel 2007, save a couple clicks when hiding rows or columns: choose Format, Row Height or Column Width, and enter 0.

Jan 05

Navigation Menu Trick

Right-click on the navigation arrows in the lower left-hand corner of the screen to display a worksheet selection menu.

Dec 16

Update Multiple Cells At Once

Update multiple cells at once: select two or more cells, type an entry in the first cell, and then press Ctrl-Enter.

Dec 08

Excel Tip: Hide an Excel Worksheet in Plain Sight

Excel Tip: Hide an Excel Worksheet in Plain Sight


By David Ringstrom, CPA


As you’re probably aware, it’s simple to hide a worksheet in Excel 2007: right-click on the worksheet tab, and then choose Hide. To unhide the worksheet, right-click on any worksheet tab, choose Unhide, and then select the sheet you wish to make visible. It’s a little more effort in earlier versions of Excel: choose Format, Sheet, and then Hide or Unhide to change the visible status of a worksheet.

Since most users know these tricks, hiding a worksheet is sometimes of little value, because someone else can simply unhide it. Protecting a workbook makes it harder, but savvy users then know that you have something to hide. In such cases, a user might visit www.lostpassword.com or search the Internet to secure an unlocking utility. However, you can take this arms race to new levels by making a worksheet “very hidden,” which will make it appear as if there are no hidden worksheets within your workbook. To do so, you must access the Visual Basic Editor:

 

  • Excel 2007: Click the Visual Basic icon in the Code section of the Developer tab of the ribbon. If you don’t have a Developer tab, click the Office button, and then choose Excel options. In the Popular section, choose Show Developer Tab in Ribbon, and then click OK.
  • Excel 2003 and earlier: Choose Tools, Macro, and then Visual Basic Editor.

Keyboard shortcut: Press Alt-F11 to access the Visual Basic Editor in any version of Excel.

Within the Visual Basic Editor you’ll need to make sure that both the Project Explorer and Properties Window are displayed, as shown in Figure 1. If you don’t see either of these, choose View, and then Project Explorer and/or Properties Window, as needed.

Figure 1: You may have to turn the Project Explorer and Properties windows on.

You’re now ready to hide an Excel worksheet in such a way that the vast majority of Excel users will never find it:

 

  1. Locate the workbook in question within the Project Explorer window. Click once on the file name if you don’t see a Microsoft Excel Objects folder.
  2. Within the Microsoft Excel Objects folder for a given workbook, you’ll see a list of worksheets, as shown in Figure 2. Click once on the worksheet that you wish to hide.

Figure 2: The Properties Window allows you to change the visible property of a worksheet.

 

  1. Set the Visible property in the Properties Window to xlSheetVeryHidden, as shown in Figure 2. Note that by default the next worksheet on the list will be selected once you change this property, so it may appear that your change didn’t take effect. However, if you click on the worksheet name again in the Project Explorer window, you’ll see that the xlSheetVeryHidden property is set.
  2. Choose File, and then Close and Return to Excel.

To test your handiwork, return to Excel, and try to unhide the worksheet. As shown in Figure 3, you should find that the Unhide command is disabled, even though the workbook contains a hidden worksheet. To make the sheet visible again, return to the Visual Basic Editor and set the Visible property for that worksheet back to xlSheetVisible.

Figure 3: The xlSheetVeryHidden will prevent the average user from discovering your hidden worksheets.




A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Dec 05

Copying Cell Contents Down

Double-click fill handle to copy current cell down, stops at the first blank in adjacent column. Or select cells & press Ctrl-D.

Dec 02

AND() Function

The AND function allows you to tests for two or more conditions in an IF statement. =IF(AND(A1>=1,A1<=100),"Between 1-100","Other")

Nov 13

Concatenation Trick

Use an ampersand instead of the CONCATENATE function. The formula =A1&”-“&B1 is much easier than =CONCATENATE(A1,”-“,B1)

Nov 12

Formatting Sets Of Rows

Use this formula with Conditional Formatting to highlight alternating sets of 5 rows: =MOD(CEILING(ROW(),5),2)

Oct 29

Three tricks for formatting rows in Excel

By David H. Ringstrom, CPA



I’m sure that you’ve worked with data sets in Excel where the information starts blurring together. In this article I discuss several techniques that I use avoid getting lost in the forest. First I’ll briefly describe the Table feature in Excel 2007 and Excel 2010, and then share some conditional formatting tricks for anyone that’s still using an earlier version of Excel.
It’s easy to change the color of every other row in an Excel 2007 or Excel 2010 worksheet:
1.     Select a single cell within your list of data.
2.     Choose Format as Table from the Styles section of the Home ribbon, and then choose a color scheme, as shown in Figure 1.
3.     As shown in Figure 2, Excel will automatically determine the cell coordinates of your table. If you choose the My Table Has Headers option, then Excel will add Filter arrows at the top. As an added bonus, if your table is longer than what you can see on a single screen, your header row contents will move into the worksheet frame when you scroll down, as shown in Figure 3.
4.     To eliminate the table, choose Convert to Range in the Tools section of the Design tab that appears when you click on the table. The formatting remains in place, so to eliminate it you can either choose new formatting, or use the Clear Formatting command in the Editing section of the Home ribbon.
Figure 1: Excel 2007’s Format as Table feature.
 
Figure 2: Excel automatically determines the cell coordinates of your table.
Figure 3: Your header row appears in the worksheet frame when you scroll beyond the first screen.
 
The Table feature is a fast way to format your data, but let’s say that you need more control over your formatting, such highlighting your data in groups of 5. You can use the Conditional Formatting feature in Excel 2007 or 2010 to do so:
1.     Select the cells that you wish to format.
2.     Click the Conditional Formatting button in the Styles section of the Home ribbon, and then choose New Rule, as shown in Figure 4
Figure 4: Conditional Formatting appears on the Home tab of the ribbon.
 
3.     Select Use a Formula to Determine Which Cells to Format, and then enter this formula:
4.     Click the Format button to assign the formatting of your choice, and then click OK.
=MOD(CEILING(ROW(),5),2)=0
5.     As shown in Figure 5, alternating blocks of 5 rows will be highlighted.
Figure 5: Conditional formatting allows us to highlight data in blocks of 5 rows at a time.
 
Note: Use this formula with Conditional Formatting if you wish to highlight every other row:
=MOD(ROW(),2)
In Excel 2003 or earlier, select the cells you wish to highlight, choose Format, and then Conditional Formatting. Change Condition 1 to Formula is and use one of the formulas shown above.
Here’s a quick rundown of how these formulas work:
·         ROW returns the current row number. This function allows you to optionally specify an address argument. However, in this case we’re referring to the current row, so there’s no need to provide a cell address.
·         CEILING rounds the row number up to the next multiple of 5 (or whatever number you specify). There are two arguments: number, and significance. We use ROW() to provide the number, and significance is the multiple that we want to round the row number to.
·         MOD returns the remainder from a division calculation, and has two arguments: number and divisor. In the case of the first formula, the results of the CEILING function are divided by 2. This means that rows 1 through 5 would round up to 5, and since 2 does not divide evenly into 5, MOD returns 1. Since we added =0 to the end of our formula, the Conditional Formatting feature in turn formats rows where MOD returns 0 instead of 1. Thus rows 1 through 5 don’t get formatted, while 6 through 10 do, while 11 through 15 don’t, and so on.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Oct 19

Create A Series Of Month End Dates

Create month-end date series- Cell A1: 10/31/09, Cell A2: =DATE(YEAR(A1),MONTH(A1)+2,0), copy down as needed.

Oct 05

Number Formatting Trick

Press Ctrl-Shift-! to apply the Number format, e.g. 1,234.56, to one or more cells that you’ve selected.

Oct 01

Removing Borders Trick

Press Ctrl-Shift-_ (underscore) to remove borders from a cell.

Sep 29

Data Entry Trick

Press Alt-Down Arrow to get an in-cell drop-down list of adjacent entries in the current column