Category: AccountingWEB Excel articles

Jun 17

What to do when Excel won’t let you insert columns

By David H. Ringstrom, CPA


From time to time you might encounter the prompt that appears when Excel thinks you can't add additional columns or rows.


This occurs when Excel considers used range of the worksheet to encompass all columns or rows. In this article I'll describe some techniques you can use to overcome this problem.


Figure 1: It's frustrating when Excel won't allow you to insert columns or rows.


The first, and usually easiest, method is to delete all columns to the right of the active area of your worksheet. If you can't insert rows, delete all rows below the active area of your worksheet.
For instance, assume you have data in columns A through M of your worksheet. To delete the remaining columns, place your cursor in cell N1, and then press Ctrl-Shift-Right. This will take you to the last column of the worksheet, which is column XFD in Excel 2007 or 2010, or column IV in Excel 2003 or earlier. Once you've done so, the cells in row 1 starting from column N through the right should be selected. Right-click on any of the selected cells, choose Delete, Entire Column, and then OK.


Further, let's assume our data goes down to row 28. Place your cursor in cell A29, and then press Ctrl-Shift-Down. This will take you to the last row of the spreadsheet, which is row 1,048,576 in Excel 2007 and 2010, or row 65,536 in Excel 2003 and earlier. Right-click on any of the selected cells, choose Delete, Entire Row, and then OK.


You may now try inserting new columns or rows. If that doesn't work, the next step is to use the Visual Basic Editor to enter a single line of code that will reset the used area of the spreadsheet:
     1.Right-click on the worksheet tab of the sheet where you can't insert columns (or rows), and then choose View Code.
     2.Press Ctrl-G to display the Immediate window, as shown in Figure 2.
 
Figure 2: The Immediate window in Excel's Visual Basic Editor.


     3.In the Immediate window, type ActiveSheet.UsedRange and then press Enter.
     4.It will appear as if nothing has happened, but the command in Step 3 forces Excel to change the Used Range of the worksheet to conform to just the area where your data is.
     5.Choose File, and then Exit to close the Visual Basic Editor.


You should now be able to insert new columns or rows as needed in your worksheet.




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.

Jun 08

Two ways to determine your top customers in QuickBooks, Excel

By David H. Ringstrom, CPA


Given the length of the Great Recession, your top customers today might not be the same folks who were your top customers even a couple months ago. In this article I'll demonstrate two ways that you can determine who your top customers are based on total sales.

The first technique involves a minor tweak to an existing QuickBooks report, while the second demonstrates a couple of tricks that I use to transform QuickBooks reports into usable data in Excel.

To quickly determine your top customers for a given time period, follow these steps:

1. In QuickBooks, choose Reports, Sales, and then Sales by Customer Summary.

2. When the report appears onscreen, change the report dates to display the period that you wish to see, and then click Refresh, as shown in Figure 1.

Figure 1: Modify the dates of the Sales By Customer Summary to display the report period in question.

3. Click the Modify Report button that appears at the top of the report window.

4. In the Modify Report window, change Sort By to Total, instead of Default, as shown in Figure 2.

Figure 2: Change the Sort By to Total, instead of Default.

5. Click OK to display the modified report on the screen.

6. To save this report for future use, click the Memorize button that appears at the top of the report window. Assign a name, such as Top Customers, and then optionally choose a Memorized Report Group. Going forward, you can access your report by choosing Reports, Memorized Reports, and then looking for your report title.

This approach is simple enough, but doesn't allow you to limit the report to a particular subset, say your top 10, 25, or 100 customers. Fortunately it's easy to do this with Excel:
1. In QuickBooks, choose Reports, Sales, and then Sales by Customer Summary.

2. When the report appears onscreen, change the report dates to display the period that you wish to see, and then click Refresh, as shown in Figure 1.

3. Click the Export button at the top of the report screen, choose New Excel workbook, and then click Export.

4. The total sales for each customer are a =SUM formula that will get corrupted by a subsequent process that we're going to do to this report, so we must convert the totals to values:

a. Right-click on the sales figure column (for instance, column D in Figure 3), choose Copy to copy the entire column to the clipboard.

Figure 3: Convert the sales figures to numbers instead of formulas.

b. Right-click again on the sales figure column, choose Paste Special, Values, and then click OK.

5. Click on cell A1, and then press Ctrl-* to select the entire report. Use the * key on the number pad of your keyboard, or press Ctrl-Shift-8.

6. Turn on the filter arrows:

  • Excel 2007/2010: Choose Sort & Filter in the Editing section of the ribbon, and then choose Filter.
  • Excel 2003 or earlier: Choose Data, Filter, and then AutoFilter.

7. Click the filter arrow in cell B1 (as shown in Figure 4), and then choose Text Filters, and then Custom (Users in Excel 2003 and earlier can just choose Custom).

Figure 4: Be sure to use the filter arrow in cell B1.

8. As shown in Figure 5, choose Does Not Equal, enter Total*, and then click OK.

Figure 5: Choosing Does Not Equal and Total* is the same as saying “does not begin with”

9. Use the worksheet frame to select all visible rows from row 2 through the end of the report, and then right-click on any of the rows you selected and then choose Delete, as shown in Figure 4.

10. Click the arrow in cell B1, and then choose All in Excel 2003 and earlier or click Select All in Excel 2007/2010.

11. Delete any blank columns, so that you're left with one column with your customer names, and a second column with the sales amounts.

12. The next step is to eliminate the word Total from the start of each customer row:

a. Click on column A on the worksheet frame to select the entire column.

b. Choose Data, and then Text to Columns.

c. When the Text to Columns wizard appears, choose Fixed Width, and then click Next.

d. Excel automatically guesses that we want to eliminate the word Total, so click Next again.

e. As shown in Figure 6, click on the Total column, and then choose Do Not Import (skip), and then click Finish.

Figure 6: The Text To Columns wizard allows you to delete the word Total from the start of each customer name.

3. You can now use the Top 10 AutoFilter feature to display a selected range of customers. To do so, click the arrow in at the top of the column that contains the sales figures. Next, choose Top 10 in Excel 2003 and earlier, or Number Filters, and then Top 10 in Excel 2007/2010. As you can see in Figure 7, although the feature is “Top 10”, you can really display the top number of your choice. The final report format is shown in Figure 8.

Figure 7: The Top 10 feature in Excel actually allows you to filter any number of customers you choose.

Figure 8: The final report format generated by using Excel's Top 10 filtering feature.

 

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 24

Transform multiple-line cell contents into columns in Excel

By David H. Ringstrom, CPA


Sometimes in Excel you may encounter a situation such as that shown in Figure 1, where each cell contains multiple lines of data. It's not a huge task to manually type a few records, but what if you have dozens or hundreds of records in this format? In this article I'll explain some simple techniques that can make quick work of transforming this data into columns.


Figure 1: It can be difficult to convert multiple lines of text in a single cell into columns
Expert Tip: If you want to recreate data similar to what you see in Figure 1, type a line of text in a cell, and then press Alt-Enter to add a new line.
The first step in the process is to use the SUBSTITUTE function to replace the non-printing new line character with another character. You must use a formula, because Excel's Find and Replace feature doesn't allow you to replace non-printing characters such as new lines. In a pinch, you can use Word to carry out such replacements, as shown in Figure 2, but it's easy to use SUBSTITUTE.


Figure 2: Word allows you to find and replace non-printing characters, but Excel doesn't.
The SUBSTITUTE function has four arguments:
·                     text – In this case, text will be a cell that contains new line characters
·                     old_text – Excel's CHAR function can generate the non-printable new line character
·                     new_text – I like to use the | symbol (often referred to as the pipe symbol) as a unique identifier in place of the new line character. This symbol usually appears above the \ key on your keyboard.
·                     instance_num – This is an optional argument that I'll omit in this case because I want to replace all of the new line characters with the pipe symbol. If I put a number here, only that quantity of new line characters would be replaced.


Excel's CHAR function just has a single argument wherein you indicate the number of the character that you want to return. New line characters are number 10, so I'll use CHAR(10) to indicate a new line. Here is a chart of all of the character symbols.


Now I'm ready to enter the formula shown in Figure 2 into cell B1. Click on cell B1, and then double-click the Fill Handle in the lower right-hand corner of the cell to copy the formula down through cell B5. As you can see in Figure 3, the SUBSTITUTE function takes the data from its original display in multiple rows and puts it into a single row, with a | symbol in between data that was on each row.


Figure 3: The SUBSTITUTE function transforms the multi-line data into a single line.
Now select cells B1 through B5, and then press Ctrl-C to copy the range to the clipboard. Right-click on cell B1, choose Paste Special, and then Values. Leave cells B1 through B5 selected, and choose Data, and then Text to Columns.


As shown in Figure 4, choose Delimited, and then click Next. Then, as shown in Figure 5, choose Other, and enter the | symbol. You can clear the checkbox for Tab, or leave it clicked – this won't have any impact if your data doesn't contain tab characters. Click Finish to complete the wizard steps. As you can see in Figure 6, the data is now transformed into columns!


Figure 4: Choose Delimited on the first screen of the Text to Columns wizard.


Figure 5: Choose Other, specify | as the separator, and then click Finish.


Figure 6: Text to columns transformed the data from rows within a single cell into columns.



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

Some unlikely uses for Excel’s SUMPRODUCT function

By David H. Ringstrom, CPA


While you may not be aware of Excel's SUMPRODUCT function, those in the know often use it for its stated purpose: multiplying two ranges together and then summing the total.
As shown in Figure 1, a single formula using SUMPRODUCT can replace the eight formulas listed in Cells D2 through D9. The SUMPRODUCT function multiplies the values in Column B by the values in Column C on a row-by-row basis, and then sums the total.
Although this is helpful, you might not be aware that SUMPRODUCT also can function like VLOOKUP or SUMIF on steroids.


Figure 1: SUMPRODUCT multiplies values together and sums the products, but can also do more.
Excel's VLOOKUP function allows you to look up data from a table based on criteria that you specify, and has four arguments:
  • lookup_value – This is the data that you want to look for in the first column of the table array.
  • table array – The table array is a range of two or more columns.
  • col_index_num – This argument allows you to specify the column within the table array for which you want to return data.
  • range_lookup – In this position you indicate FALSE if you're seeking an exact match for the lookup_value, or TRUE if you want an approximate match. For instance, you'd use FALSE to look up the price of an inventory item, or TRUE if you're determining which tax bracket an income level falls into.


Figure 2: VLOOKUP allows you to look up a single value based on a single criteria.
As shown in Figure 2, VLOOKUP returns $85,106 from Column E for the first instance of Apples that appears in Column D. The additional instances of Apples are ignored. If you need to add up multiple values based on specific criteria, then SUMIF is a better choice. This function has three arguments:
  • range – This is a column or row where you want to look for specified criteria.
  • criteria – This is the same as the lookup_value for VLOOKUP.
  • sum_range – This is a column or row from which you want to add up numbers whenever the criteria is found in the range.


Figure 3: SUMIF allows you to add up multiple items, but also is limited to a single criteria.


Figure 4: SUMPRODUCT can return the same results as SUMIF.
 
As shown in Figure 3, SUMIF returns $396,495. In this case, every time it found a match in Column D on the word Apples, it added up the corresponding value in Column E. Like VLOOKUP, you can only search based on a single criteria. Fortunately, SUMPRODUCT allows you to add up values based on multiple criteria.
However, let's first use SUMPRODUCT to match based on a single criteria, as shown in Figure 4. In this case it returns the same result as SUMIF because we only provided a single criteria.
Conversely, in Figure 5, SUMPRODUCT returns $272,584 because we specified that we only want sales for apples sold in North Georgia.


Figure 5: SUMPRODUCT also can return results based on multiple criteria.
As shown in Figure 5, the formula looks at Cells A2 through A19 for the words North GA, and at Cells D2 through D19 for the word Apples. When both criteria are met, SUMPRODUCT adds up the corresponding values from Cells E2 through E19.


Figure 6: SUMPRODUCT also can return the number of matches that meet criteria you specify.
Finally, as shown in Figure 6, if you only specify criteria and omit the range to sum, then SUMPRODUCT returns the number of items that match the criteria that you specify.



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

What’s the FREQUENCY? Using Excel’s FREQUENCY function

By David H. Ringstrom, CPA


From time to time you may want to determine how items fall within specific ranges. For instance, a teacher may wish to know how many students earned A's, B's, C's, and so on. A real estate portfolio manager may wish to know how many buildings fall within ranges of 250,000, 500,000, and one million square feet, respectively. In such cases, Excel's FREQUENCY function is the right tool for the job.


The FREQUENCY function has two arguments:
  •  data_array – a range of cells containing numeric values
  • bins_array –a range of cells containing bins into which the numeric values should be grouped
                    
FREQUENCY has a special characteristic, in that you don't simply type it into a cell and press Enter. Instead, you must select the cells where you want to put the FREQUENCY function, type the formula, and then press Ctrl-Shift-Enter. If you simply press Enter, then FREQUENCY may return an incorrect result. Let's look at a simple example.
As shown in Figure 1, let's assume that a teacher wishes to determine how many students earned each letter grade. Columns A and B of Figure 1 contain student names and grades. Columns D and E contain letter grades and the top value of each letter grade range.


Figure 1: Cells B1:B10 will serve as the data_array, while cells E1:E5 will serve as the bins_array.
Now that we've established our data, we're ready to use the FREQUENCY function. First, we'll select cells F1 through F5, as shown in Figure 2.


Figure 2: Select cells F1 through F5 before you type the FREQUENCY function.
 Once you've selected cells F1 through F5, type the formula shown in Figure 3, and then press Ctrl-Shift-Enter. FREQUENCY will not function correctly if you simply press Enter and try to copy the formula to the adjacent cells.


Figure 3: Be sure to press Ctrl-Shift-Enter after you complete the FREQUENCY function.
As shown in Figure 4, when you press Ctrl-Shift-Enter, Excel fills cells F1 through F5 with the FREQUENCY function. Also notice the curly brackets that Excel adds around the formula. These indicate an array function, and these brackets can only be added by pressing Ctrl-Shift-Enter after you type or edit the formula. Unlike most formulas that aggregate results into a single cell, FREQUENCY requires you to select a multiple cell range in order to function correctly.


Figure 4:  Excel automatically fills cells F1 through F5 and adds curly brackets around FREQUENCY.
 
However, FREQUENCY isn't just for teachers. As I mentioned at the start of the article, a portfolio manager might wish to know how many buildings are defined as small, medium, or large, based on square footages of say 250,000, 500,000, and one million. Figure 5 shows an example.


Figure 5: Press Ctrl-Shift-Enter after you enter the formula shown in Figure 5.



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

Excel Tip: Converting numbers formatted as <1> to -1

By David H. Ringstrom, CPA


From time to time you may encounter financial data where negative numbers are enclosed in brackets, such as <100>. In most cases, Excel treats such numbers as text, which means such numbers won't be included in totals or other arithmetic functions.
Typically such numbers will be left-aligned, as shown in Figure 1.


Figure 1: Text-based numbers are typically left-aligned.
In this article I'll discuss two approaches you can use to convert such text-based inputs to negative numbers. I'll also share a formatting technique you can use if you want to enclose negative numbers in such brackets instead of parentheses.


The first approach for converting a number like <1> to -1 involves Excel's Find and Replace feature:
  1. Select the range of cells that contain numbers surrounded by < >. It doesn't matter if you select cells that have numbers without brackets, but it's helpful to limit the area that you're searching to avoid unintended replacements.
  2. In any version of Excel, press Ctrl-H to display the Find and Replace dialog box shown in Figure 1.
  3. As shown in Figure 2:
                    a.      Enter < in the Find What field
                    b.      Enter – in the Replace With field
                    c.      Click Replace All

Figure 2: Replace < characters with a minus sign.

 
      4.      Press Ctrl-H again, and as shown in Figure 3:
                    a.       Enter > in the Find What field
                    b.      Leave the Replace With field blank
                    c.       Click Replace All
 


Figure 3: Replace > characters with a blank field.
 
At this point all of the numbers surrounded by < > will be negative numbers and no longer treated as text.
Alternatively, you can tackle this problem formulaically. To do so, I'll use several different Excel functions:
  • IF – The IF function allows us to carry out a true/false test, return a result, or perform a calculation based on the result of the test. IF has three arguments: logical_test, value_if_true, and value_if_false.
  • ISNUMBER – The ISNUMBER function allows us to test whether a cell contains a number. This function has a single value argument, and returns TRUE if a cell contains a number or FALSE if it contains text or is blank.
  • VALUE – The VALUE function converts a number stored as text into a value, and has a single text argument.
  • MID – The MID function allows us to extract text from the middle of a string, which is another way to refer to text within a cell. This function has three arguments: text, start_num, and num_chars.
  • LEN – The LEN function returns the length of text within a cell, and has a single text argument.
Now let's put these functions together into a single formula.
1.      As shown in Figure 1, enter these inputs into a blank worksheet:
                    A1: <1>
                    A2: 20
                    A3: <30>
                    A4: 400
                    A5: <5,000>
2.      Enter this formula in Cell B2, as shown in Figure 4:
=IF(ISNUMBER(A1),A1,-VALUE(MID(A1,2,LEN(A1)-2)))
 
Figure 4: You can use a formula to convert text-based numbers to values.
 
As discussed above, IF has three arguments:
  • logical_test: the ISNUMBER function determines if the data in Cell A1 is a number or not.
  • value_if_true: If ISNUMBER returns true, then Cell A1 contains a number, so I'll simply return that value.
  • value_if_false: If ISNUMBER returns false, I know that I have a number surrounded by < > that I wish to remove. In this case I'll use the VALUE and MID functions together.
As shown above, MID has three arguments:
  • text – In this case, I refer to Cell A1 for the text I wish to shorten
  • start_num – I know that I want to eliminate the starting < character, so I instruct MID to start at the second position
  • num_char – I also want to eliminate the trailing >, so I'll use the LEN function to determine how long the text in Cell A1 is, and then subtract 2 from that number. Thus LEN(A1)-2 would return 1 for Cell A1. LEN(A3)-2 returns 2, and LEN(A5,2) returns 5 (the comma counts as a character).
The MID function returns a text-based number, which I convert using the VALUE function. I added a minus sign before VALUE to return a negative number.


Finally, I promised to share a technique that you can use if you want to use < > instead of parentheses to format a spreadsheet:
  • In any version of Excel, press Ctrl-F1 to display the Format Cells dialog box.
  • Choose Custom from the Number tab.
  • Erase the Type field, and enter this format code, as shown in Figure 4:
#,##0.00_);<#,##0.00>
Alternatively, use this code if you want to format the numbers with dollar signs:
$#,##0.00_);<$#,##0.00>
Or, insert [Red] if you with to make negative numbers red:
#,##0.00_);[Red]<#,##0.00>




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

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

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

Aug 06

First Impressions of Excel 2010

.by David Ringstrom, CPA


As you may have heard, the next version of Microsoft's venerable Office suite recently entered “technical preview,” or public beta. While Excel 2007 introduced some dramatic changes, Excel 2010 seems to offer mostly refinements and only a few wholesale changes. Thus Excel 2007 users should have an easy time transitioning to Excel 2010, but anyone still using Excel 2003 or earlier will need to make the transition to the Ribbon-based user interface. Fortunately, this interactive guide from Microsoft will ease your pain.

Microsoft is slowly releasing information about the changes in Excel 2010, and so some new features aren't fully documented yet. I'll be posting in-depth articles about Excel 2010 in the coming months, but for now, where's what to expect when you first launch Excel 2010:
  • The big Office button in the left-hand corner has been replaced with a small green button, as shown in Figure 1. The resulting menu has also been reworked, as shown in Figure 2.


Figure 1: Presenting Excel 2010.

  • The Ribbon itself remains mostly unchanged, other than to introduce new Excel features and a new background look. However, you can now rework the Ribbon to suit your needs. This means you can add custom tabs or turn off any of the existing tabs, which allows you to customize Excel 2010 to meet your needs. Don't worry: it's easy to reset the Ribbon to its default state when necessary.
  • The Info tab shown in Figure 2 gives you a great deal of information about your document. Interestingly, the Info tab in Word 2010 lists the total editing time for the document, but Excel's Info tab does not.


Figure 2: The new Info tab provides statistics about your document.


Figure 3: Excel 2010 allows you to create in-cell charts known as Sparklines.

  • The Slicer is a new filtering feature provides a visual interface for filtering pivot table and other database data. Instead of using the traditional drop-down lists to limit the data shown in a pivot table, the new Slicer gives you a graphical mechanism to filter the data shown in your pivot table. Bill Jelen, aka Mr. Excel has created a three-minute video that demonstrates this feature.
  • Excel 2010 has a Screenshot/Screen Clipping feature that will help make it easier to document your spreadsheets by simplifying the screen capture process.
  • You'll have the ability to block users from opening or saving Excel files in certain data formats.
  • As shown in Figure 4, the Print command in Excel 2010 has been reworked to merge print preview and page setup choices into the print process.


Figure 4: Print, Preview, and certain Page Setup Elements have been merged into a single screen

  • If your computer reboots while you have an Excel document open, Excel 2010 automatically launches itself at start-up, and attempts to automatically recover the documents you were working on.
  • A new feature currently known as Project Gemini will reportedly allow Excel users to analyze millions of records from a SQL Server database in Excel. This feature was not included in the technical preview.
  • The right-click menu for worksheet cells contains new paste options, as shown in Figure 5.


Figure 5: New icons eliminate the need to go to the Paste Special dialog box for common tasks.

 

 

  • A new feature automatically saves a copy of your unsaved documents for up to 4 days. The caveat is that Excel has to create an AutoRecover copy first. However, if you inadvertently close without saving a document that you've been working on for 10 minutes or more, you may just be able to recover your work.
  • There are certainly other new features in Excel 2010, but the aforementioned list shows the items that have caught my attention thus far.  You can learn more by following these blogs:
  • Microsoft Excel Team Blog
  • Microsoft Office 2010 Engineering
And of course, don't miss Office 2010 The Movie.



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

Apr 17

Tricks for hiding and unhiding Excel rows and columns

By David H. Ringstrom, CPA


Hiding and unhiding rows and columns are mundane tasks that many users take for granted. However, sometimes simple tasks can trip up Excel users, like unhiding just one row or column within a hidden set. Other users don't know simple keystroke commands that can streamline hiding and unhiding columns or rows. In this article, I'll explore these techniques as well as discuss two powerful alternatives to manually hide and unhide rows and columns. I'll also discuss how to re-enable an Excel keyboard shortcut that's disabled in any operating system subsequent to Windows XP.


Hiding Rows and Columns
Let's first explore the traditional approaches to hiding rows and columns. Going forward, I'll only explain rows – simply replace the word Row with Column in any menu commands that I describe if you want to hide or unhide columns. First, select the row or rows that you wish to hide or unhide, and then carry out these steps:
  • Excel 2007 and later: On the Home tab, choose Format in the Cells section of the ribbon, and then choose Hide & Unhide, and then either Hide Rows or Unhide Rows.
  • Excel 2003 and earlier: Choose Format, Row, and then either Hide or Unhide.
Tip: Remember, to unhide rows, you must select rows on either side of the hidden set. If you're trying to unhide rows at the top of the worksheet, click on the first visible row, and then move your mouse up to the top of the screen.


Keyboard Shortcuts
Alternatively, you can select a row or rows, and then press Ctrl-9. To unhide rows, press Ctrl-Shift-9. For columns, use Ctrl-0 (that's a zero) or Ctrl-Shift-0, respectively. There's a catch with the latter shortcut, though. By default, Windows Vista, Windows 7, and Windows 8 use Ctrl-Shift-0 (zero) as a keyboard shortcut for changing the keyboard layout. This means that when you move from Windows XP to a newer version of Windows, Ctrl-Shift-0 no longer works in Excel unless you change an arcane Windows setting shown in Figure 1.


Figure 1: The Switch Keyboard Layout option in Windows Vista and later blocks Ctrl-Shift-0 for unhiding columns in Excel.


Windows 8:
  • Click on Language within the Control Panel.
  • Click Advanced Settings, and then click the Change Language Bar Hot Keys link.
  • Click Change Key Sequence, select Not Assigned in the Switch Keyboard Layout section, and then click OK as needed.
Windows Vista or Windows 7:
  • Click Region and Language within the Control Panel.
  • Choose the Keyboards and Languages tab, and then click Change Keyboards.
  • Click the Advanced Key Settings tab, and then click Change Key Sequence.
  • Select Not Assigned in the Switch Keyboard Layout section, and then click OK as needed.
Windows XP:
  • No Control Panel changes are necessary.
Unhide Selectively
Sometimes you may want to unhide just a single row or column. There are two ways to do so:
1. Press Ctrl-G to display the Go To window, type in the address of a cell in the row or column that you wish to unhide, and then click OK, as shown in Figure 2. Carry out the corresponding menu command or keyboard shortcut to unhide the row or column. You can also use menu commands to display the Go To dialog box:
  • Excel 2007 and later: Choose Find and Select on the Home tab, and then click Go To.
  • Excel 2003 and earlier: Choose Edit and then Go To.
2. If you don't know the exact address of the cell you're looking for, press Ctrl-F to display the Find window and search for a word within the hidden column or row. As with the Go To command, Excel will select the hidden cell, which you can then unhide. You can also use menu commands to display the Find dialog box:
  • Excel 2007 and later: Choose Find and Select on the Home tab, and then click Find.
  • Excel 2003 and earlier: Choose Edit, and then Find.
Figure 2: The Go To dialog box allows you to navigate to a hidden cell when you need to selectively unhide a row or column.


Group Rows or Columns
Excel's Group feature is an effective alternative to manually hide or unhide rows and columns. Select the rows or columns you wish to hide, and then carry out this command:
  • Excel 2007 or later: Click the Group icon in the Outline section of the Data ribbon.
  • Excel 2003 and earlier: Choose Data, Group and Outline, and then Group.
As shown in Figure 3, Excel adds a button outside the worksheet frame that you can use to toggle the hidden or visible status of rows or columns. To remove grouping, select the group, and then issue the corresponding Ungroup command, which is adjacent to the Group commands described above.
Figure 3: The Group feature allows you to expand or collapse a set of rows or columns with a single mouse click.


Custom Views
Many Excel users overlook the Custom Views feature, which among other things, allows you to save sets of hidden rows or columns. Before you start hiding rows or columns, first create a view that displays the entire worksheet:
  • Excel 2007 and later: Choose Custom Views in the Workbook Views section of the View ribbon. Click Add, and then assign a name, such as All Columns. Make sure that Hidden Row, Columns, and Filter Settings is selected, and then click OK.
  • Excel 2003 and earlier: Choose View, and then Custom Views. From there, the commands are the same as described in Excel 2007.
Next, hide rows and/or columns as desired, and then save a second custom view. You can now toggle between views as needed. Issue the Custom Views command, select a view from the list, and then click View.


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

Apr 17

Tricks for opening and closing multiple Excel files

By David H. Ringstrom, CPA


From time to time you may find yourself immersed in a large project that involves several related Excel workbooks. In such cases it can be tedious to manually open each Excel file when you need everything available at once. Excel has long had a helpful feature that is hidden in plain sight: the Save Workspace command. This command creates a bookmark file with an .XLW extension that you can use to automatically open a group of related spreadsheets. In this article I'll explain Excel's Workspace feature, as well as how to close all open worksheets at once while still leaving Excel open.

Workspace feature

A workspace is comprised of whatever spreadsheets are open at the time that you issue the Save Workspace command:

  • Excel 2007: Choose Save Workspace in the Window section of the View ribbon.
  • Excel 2003 or earlier: Choose Save Workspace on the File menu.

In either case, after making the menu choice, you're presented with a traditional Save dialog box from which you can choose where to save your workspace file. Excel 2007 will prompt you to save each file within the workspace, while Excel 2003 and earlier won't prompt you until you close an individual file within the workspace.

To reopen your workspace, you can either choose the .XLW file from your Recently Used file list, or open the file manually:

  • Excel 2007: Click the Office button, choose Open, and then select the .XLW file.
  • Excel 2003 or earlier: Choose File, Open, and then select the .XLW file.

You can further refine your search in any version of Excel. To do so, choose Workspaces (.XLW) from the Files of Type list, as shown in Figure 1.

 

 

Figure 1: Workspaces (.XLW files) display in the list of available files when you issue the Open command, but you can also filter the list to only show Workspace files.

Excel 2007 Trick: Click the pushpin next to your .XLW file to lock your workspace onto the Recently Used File List. It may shift down the list as you open other spreadsheets, but it won't scroll off the list as long as its pushpin is depressed.

 

 

Figure 2: Click the pushpin to lock a workspace onto your Excel 2007 Recently Used File List.

Closing All Files at Once

Sometimes during the day you need to clear the decks of all open files, but leave Excel open. You can certainly close each file individually, but versions through and including Excel 2003 had a far easier way to perform this task: Hold down the Shift command before you click on the File menu. As shown in Figure 3, the Close command changes to Close All, which enables you to close all open workbooks at once. Although the Shift key trick doesn't work in Excel 2007, you can actually create your own custom shortcut:

  • Right-click anywhere on the Excel 2007 ribbon, and then choose Customize Quick Access Toolbar.
  • Choose Commands Not in the Ribbon from the Choose Commands From List, and then add Close All to your Quick Access toolbar.
  • Optionally move the Close All command to the top of your Quick Access Toolbar list, as shown in Figure 4, and then click OK.

If you moved the Close All command to the top of your list, you can now press Alt-1 to issue the Close All command whenever you wish. As shown in Figure 5, the shortcut code for every button on the Excel 2007 interface is revealed when you press the Alt key.

 

 

Figure 3: Hold down the shift key before you choose the File menu in Excel 2003 or earlier to display the Close All command.

 

 

 

Figure 4: You can add a Close All command to your Excel 2007 Quick Access Toolbar.

 

 

 

Figure 5: Shortcut keys for every button on the Excel 2007 ribbon appear when you press the Alt key once.

 
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

Apr 17

Buried gold: Excel’s Form command

By David H. Ringstrom, CPA


Many users are not aware that Excel has long had a feature that enables you to browse through a table of data one record at a time. As shown in Figure 1, the Form command provides an easy way to navigate through a table of data. This command is easy to access in Excel 2003 and earlier versions, but is deeply buried in Excel 2007. In this article I'll show you how to use the Form command to make quick work of reviewing a list of records.


Figure 1: The Form command allows you to navigate through a table of records.


Enable the Form command

It's easy to use the Form command in Excel 2003 or earlier versions: Select a single cell within a table of data, choose Data, and then Form. While many Excel 2007 users will simply think that the Form command is no longer
available, you can easily uncover it:

  • Right-click anywhere on the Ribbon and choose Customize Quick Access Toolbar.
  • Choose Commands Not In the Ribbon, and then scroll down to Form, as shown in Figure 2, or press the letter F four times after you click on.
  • Click the Add button, and then click OK.

As shown in Figure 3, you can now click the Form button on the Quick Access toolbar to display the Form window shown in Figure 1.




Figure 2: You must manually add the Form command to your Quick Access toolbar in Excel 2007.




Figure 3: The Form command is now available on the Quick Access Toolbar.
As shown in Figure 1, the Form window has several buttons:


  • New: This button allows you to add a new record to the table. Complete the input fields, and then click New again to add a new record to the bottom of your list.
  • Delete: This button deletes the record from the list, and cannot be undone.
  • Restore: If you've made edits to a record within the Form window, the Restore button will undo your changes. Otherwise your changes are automatically saved when you move to a new record.
  • Find Prev: This allows you to navigate to the previous record in the list.
  • Find Next: This allows you to navigate to the next record on the list.
  • Criteria: You can filter your list to see just certain records, such as the Atlanta region. Click Criteria, add criteria to the corresponding fields, and then click Form to view only records that meet the specified criteria. To eliminate criteria, click the Criteria button, click Clear, and then Form.
  • Close: Closes the Form window.

Criteria trick: You can use wild cards, like *es to search just for fruit that ends in “es”, like apples, oranges, and mixed berries. You can also use question marks to mask selected characters, like ?o?th GA to view North GA and South GA at the same time, but exclude Mid GA.

 
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

Apr 17

Managing Excel Add-Ins

By David H. Ringstrom, CPA


Add-ins are programs that plug-in into Microsoft Excel to add additional functionality. Some add-ins expand Excel's core functionality, while others allow third-party products to add features to Excel's environment. In this article I'll discuss how to enable some of Excel's hidden features, as well as give you some pointers on what to do when third-party add-ins vanish from Excel.

Hidden Features
Excel ships with several add-ins that you can manually enable. Some of these include:

It's easy to enable these add-ins:

  • Excel 2007: Click the Office button, choose Excel Options, and then click Add-Ins. Choose Excel Add-Ins from the Manage section at the bottom of the Add-Ins window as shown in Figure 1, and then click Go. As shown in Figure 2, select any add-ins that you wish to include.

Figure 1: Use the Manage section to work with Add-ins in Excel 2007.

 

 

 

Figure 2: You're just a few mouse clicks away from enabling several hidden Excel features.

 

 

 

  • Excel 2003 and earlier: Choose Tools, Add-Ins, and then select any add-ins you wish to enable, as shown in Figure 2.

In other cases, third-party applications may automatically install add-ins in Excel. For instance, Adobe Acrobat often adds a custom menu or toolbar in Excel. Some of my clients work in the financial industry and rely on add-ins from Bloomberg or Reuters that enable users to return prices and other data on financial products directly into an Excel spreadsheet. Most add-ins are well-behaved, and you may tend to take them for granted — until the day that you launch Excel and find that your add-in functionality has vanished. Much like a mother bear with cubs, Excel is very protective of its operating environment. If Excel determines — rightly or wrongly — that an add-in has caused an Excel crash, the add-in is benched and put on the disabled list. Excel will generally warn you in such cases, but in the case of a shared or public computer, someone else may choose to disable the add-in without you knowing. In such cases, the menu or toolbar that the add-in provides simply vanishes. Fortunately, it's easy to re-enable the add-ins if you know where to look:

 

 

  • Excel 2007: Click the Office button, choose Excel Options, and then click Add-Ins. Choose Disabled Add-Ins from the Manage section at the bottom of the Add-Ins window (shown in Figure 1), and then click Go. As shown in Figure 3, any disabled add-ins will appear on the Disabled Items list. You can enable such items one at a time, and then click Close.

 

 

Figure 3: Use this window to re-enable disabled Excel add-ins.
 

 

  • Excel 2003 and earlier: Choose Help, and then About Microsoft Office Excel. Click the Disabled Items button shown in Figure 4 to display the Disabled Items window shown in Figure 3.

 

 

 

Figure 4: The Disabled Items button is fairly well hidden in Excel 2003 and earlier.

 

 

Excel 2007 tip: Although most add-ins have a .XLA extension, others are known as COM add-ins, and don't appear in the Add-Ins list shown in Figure 2. Excel 2007 users can easily disable unwanted COM add-ins: Click the Office button, choose Excel Options, and then click Add-Ins. Choose COM Add-Ins from the Manage section at the bottom of the Add-Ins window as shown in Figure 1, and then click Go. As shown in Figure 5, you can then add or remove COM add-ins as desired. Users of earlier versions of Excel can use the free OfficeIns tool to manage add-ins.

 

 

 

Figure 5: Excel 2007 makes it easy to manage COM add-ins like Google DeskTop Office.

 
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

 

Apr 17

Three Excel menus you may have overlooked

By David H. Ringstrom, CPA


Sheet Navigation

Everyone is familiar with using the arrow buttons in the lower left-hand corner of the Excel screen to push worksheet tabs back and forth across the screen. However, try right-clicking on these buttons instead, and you'll see a menu similar to Figure 1. Simply click on a sheet name to instantly activate that worksheet.

 

 

Figure 1: Many Excel users don't know that you can right-click on the navigation arrows.


Move Rows or Columns

Sometimes you need to rearrange columns or rows on your worksheet. Many users resort to inserting new, blank columns or rows, cutting the data to be moved, and then pasting it into the new location. Instead, there are two faster ways rearrange your worksheet:
 

  • Select the columns or rows you wish to move
  • Use your right mouse button to grab the border of your selection, and then move the columns or rows to the new location.
  • When you release your mouse, the menu shown in Figure 2 will appear. If you forget and use your left mouse button, you'll encounter a different prompt. Press Escape and then try again.

Figure 2: This hidden menu appears when you use the right-mouse button to drag a selection of cells.
Of course if you're just repositioning rows, you can bypass this menu entirely: simply hold down the Shift key while you move the columns or rows into their new position.


Hide and Unhide Worksheets

Most Excel 2007 users know that it's possible to hide or unhide worksheets:
 

  • Choose the Format icon in the Cells section of the Home menu
  • Choose Hide & Unhide
  • Choose Hide Sheet or Unhide Sheet.

However, there's an even easier way: As shown in Figure 3, right-click on a worksheet tab, and then choose Hide. You can also right-click on any worksheet tab and choose Unhide to display the Unhide Sheet dialog box. Although you can hide multiple worksheets at once — hold down the Ctrl key as you click on each worksheet, and then issue the Hide command — you must still unhide sheets one at a time. Anyone using Excel 2003 or earlier won't have this option and instead must choose Format, Sheet, and Hide or Unhide. In addition, you'll notice a new Protect Sheet command, which toggles to Unprotect Sheet when the worksheet is protected.

 

Figure 3: Excel 2007 has Hide, Unhide, and Protect Sheet commands on the worksheet menu.

 
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