Excel 2010 – 12 Time Saving Tips
1. Add content or formatting to multiple sheets at once
You can add content or apply formatting to two or more of the sheets on a multisheet worksheet by “grouping” the sheets together. When you group multiple sheets, any content or formatting that you add to one sheet also gets added to all the other sheets, so you can add a row of headers to one sheet and have it automatically appear on all the sheets that are grouped together with it.
To group all the sheets in a worksheet, right-click on any of the tabs in the lower left of the window, and click Select All Sheets. If you only want to select two or more individual sheets, hold down the Ctrl key and click on the tabs of the sheets that you want to format or edit at the same time. When two or more sheets are grouped, Excel adds the word “[Group]” (in square brackets) after the sheet’s name in the title bar.
2. Use a Named Range in a Formula in Excel
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 Formulas 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.
3. Convert an Excel Formula Result into Static Text
Here’s a simple tip that can save a lot of manual labor. For example, you often import columns of text data, then use the Excel’s Concatenate function to combine two columns of data (for example, first and last names) into a third column (full names), and then you want to delete the now-redundant material in the original two columns. If you merely delete the original two columns, the results in the new column would disappear with it, so you need to convert the new column into static text first. To do this for a single cell, select the column or cells that you want to convert to static text, press F2 to open the formula bar, press F9 to evaluate the formula, and the Enter key. To do this for multiple cells, select the cells you want to convert, press Ctrl-C to copy them to the clipboard, press Ctrl-V to paste, and then from the dropdown Paste menu, select the first icon under Paste Values to paste the result of the formula in place of the formula itself.
4. The Quickest Way to See a Sum or Average
Type a few numbers in some adjacent cells, or highlight some numbers in existing cells. Now look down at the status bar at the foot of the window. Excel displays the average of the numbers, a count of the cells, and the sum. You can also use this trick with non-adjacent cells. Here’s how: click on one cell, then hold down the Ctrl key and click on a cell with a number that you want to add to the number in the first cell. Continue to add numbers by Ctrl-clicking in additional cells. The average, count, and sum in the status bar get updated each time you click another cell. By the way, when you use this tip, Excel ignores any cells you click that contain text or graphics instead of numbers.
5. Get More Information from Excel’s Status Bar
It’s easy to ignore the wealth of data that Office apps provide at a glance on the status bar. By default, Excel’s status bar displays, among other things, the Average, Count, and Sum of the selected cells. But if you right-click on the status bar, a menu lets you customize the display. You can add, for example, the Minimum and Maximum of the currently selected items or the Numerical Count—which means “the count of cells with numeric data,” as opposed to Count—which means “the count of cells that contain anything.”
6. 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 on Edit Custom Lists, and in the Customs List dialog, click NEW LIST and enter your list of words.
7. Line Up Multiple Sheets in the Same Worksheet for Inspection
How do you get two sheets from the same worksheet to appear on the same screen? Simple. On the View tab, click New Window, and then View Side by Side. By default, the Synchronous Scrolling option is turned on, so that you can scroll through both pages by dragging the slider bar in one of them. But you can turn off Synchronous Scrolling if you prefer to scroll through each sheet separately.
8. Conditional Formatting with Negative Numbers in Excel
Excel’s built-in conditional formatting gets new powers in Excel 2010. Now you can apply one of the pre-built color-coded conditional formatting options to data that includes negative numbers. This can give you quick graphic clues to the way in which profits and losses, for example, fit into a pattern that’s easier to detect graphically than by looking at a column of numbers. You can set this conditional formatting by clicking Home à Conditional Formatting à Data Bars, and then choosing a color set in the Gradient Fill gallery. The results are visible in the screen shot in column P (as shown below).
9. Display the Actual Cell Values When Creating or Editing a Formula
Here’s how to switch between displaying the cell addresses in a formula and the actual values in each cell. Use any method that displays a formula—for example, when the formula of the current cell is visible in the formula bar, or when you’re creating a formula for the first time, or after pressing Ctrl+~(tilde) to display formulas throughout the worksheet. In the formula you want to find out about, select the cell addresses, and press F9. The highlighted addresses are replaced by the values of all the cells referenced in the formula. Press Esc to return to normal display. The screenshot below shows a formula that normally displays the address D12:O12, but when you select that address and press F9, the actual values appear.
10. Highlight All Cells Referenced by a Formula
When you’re debugging a worksheet, you can easily navigate through all the cells referenced in a formula. Highlight the cell and press Ctrl-[ (that’s Ctrl-open-square-bracket). Excel highlights all the cells referenced by the formula, and moves the current selection to the first of the referenced cells. Press Enter, and the selection moves to the next referenced cell, and continue to press Enter to move though the rest of the referenced cells. In the screen shot below, the user was originally in cell D35 and pressed Ctrl-[. This highlighted D12, D26 and D35; and D12 became the current cell.
11. Tidy Up Your Charts
If you’ve ever created two or more charts on a worksheet, you know how tricky it can be to align them and make them all the same size. Here’s the easy way. Click on the first chart to select it, then hold down the Ctrl key and click on the other charts that you want to align with each other. When all the charts you want to align are selected, right-click on any one of them and choose Size and Properties. This opens the Format Shape dialog, and the measurements that you enter in the dialogue will apply to all the selected charts. After making the charts the same size, go to the Drawing Tools tab and click on Format. Use the Align dropdown menu on the ribbon to align the selected charts and to distribute them evenly either horizontally or vertically.
12. Use the Ctrl key for quick navigation
When you want to move quickly to the left, right, top, or bottom cell in a data set, just press Ctrl and one of the arrow keys. Let’s say you want to select the cells in the current row of the data set, but only the cells with numbers, not the labels—for example, sales figures for January through December. If the current cell is in the middle of the row (for example, the cell with the sales figure for May) press Ctrl-Left to go to the first data cell in this set (the cell with the sales figure for January), then hold down the Shift key and press Ctrl-Right to select all the sales figures for January through December.
Check out SoftwareMedia.com for great deals on Microsoft Excel 2010 and other discount software.
Article Contributor: Satish Kumar