Archive for the ‘Excel Tips’ Category

Excel Tips: Name versus Amount

To input data beginning with zero (for a Suite number, for example), first input an apostrophe like so: ‘00724.  Otherwise, Excel will automatically delete the zero prior to the other numbers.

Excel Tips: Selecting Multiple Cells

To quickly select multiple cells in a row or column, hit “F8” to activate Extend and then use one of the four arrow keys for whichever direction in the table that you would like to highlight.  You can then perform a host of functions on the selected cells.  This is especially useful when formatting cells.

Excel Tips: Control + Tab

Use “Control + Tab” to quickly jump to a different excel spreadsheet.  This comes in handy when you are working in multiple spreadsheets.  Similarly, you can use “Alt + Tab” to jump to a different window altogether.

Excel Tips: Grouping vs Hiding Celling

Grouping rows and/or columns can be preferable to hiding them.   When rows or columns are grouped, Excel creates a small “+” button along with a header column or row with a little “1” and “2” header.   Clicking on these “+” buttons will quickly expand or contract the selection or header columns/rows.   Try it…it is pretty cool.

Excel Tips: Tracing Formulas

“Trace Dependents” and “Trace Precedents” are terrific ways to audit a large workbook.  We use these frequently when we inherit complicated spreadsheets from others.  These tools allow you to quickly view which cells and formulas in the entire workbook pull from the target cell in question.

Excel Tips: Quick Way to Format Cells

Did you know that you can hit Ctrl + 1 to create a quick short cut to the menu item of Format Cells.  From here you can change the Category, the Alignment, the Font, the Border, the Fill or the Protection on any given cell.

Excel Tips: Automatically Fill a Row or Column with a Custom List of Names

Excel automatically fills a row with the names of months or weekdays when you type in the first few items in the row, then select the cells and drag the “fill handle” at the lower right corner of the selection to extend the series. If you have a series of words or names that you frequently enter by hand, create a custom autofill list. Click on File, then Options, and then Advanced. Scroll down almost to the foot of the Advanced menu. Under the General heading, click Edit Custom Lists…, and in the Customs List dialog, click New List and enter your list of words.

Excel Tips: Removing Existing Hyperlinks

To remove a hyperlink from a cell (but keep the cell’s contents), right-click the cell and choose Remove Hyperlink from the shortcut menu. To remove all hyperlinks in a range, select the range, right-click, and choose Remove Hyperlinks. This command works even if the selection includes cells that don’t contain a hyperlink. To remove all hyperlinks on a worksheet, press Ctrl+A to select all cells. Then right-click any cell and choose Remove Hyperlinks. Depending on the location of the cell pointer, you may need to press Ctrl+A twice to select all cells.

Excel Tips: Format Painter

Format Painter is available in all of the Office 2010 tools. It is simple to use and the perfect shortcut to redesigning cells or text to match something that you already have. The steps are quite simple:
1. Highlight any text or cell in your spreadsheet or document.
2. Click the Format Painter button, located on the Home tab in the Clipboard group on the left-most side of your screen.
3. Highlight the new text or cell.

Excel Tips: Use a Named Range in a Formula in Excel

One major convenience of a named range is that you can use it a formula instead of a set of cell addresses. Press F2 to open the formula bar, start writing or editing your formula, and place the cursor where you would normally use cell addresses. On the Formula tab in the Ruler, click on Use in Formula and either select one range name from the dropdown menu or select multiple names from the Paste Name dialog box shown here.