Whether you’re an Excel power user or just reasonably experienced with spreadsheets, there are generally still things you can learn about Microsoft’s program, and points which can be refreshed in your memory. So, we’ve put together this compilation of 14 tips for those of you using Excel 2010, going over some important features – and making sure you’re making the most of them. Also, some of these tips cover essential Intermediate to Advanced-level skills that you should know no matter what version of Excel you use.
This list of tips and tricks is for “Basic to Intermediate” Microsoft Excel users because they assume some basic understanding of how Excel works. Advanced users will likely glean something, too, and for twelve more handy tips, see this article. At any rate – on with the tips!
SEE ALSO: 20 Excel Tips for Becoming a Spreadsheet Pro
Open the Office window on two different views
Here’s a tip that applies to both Word and Excel 2010 in slightly different ways. By splitting the spreadsheet/document window into two separate panes you can view and edit two widely separated parts of a document at the same time. That means that, in Word for example, you can work on the first chapter of your novel in the top pane and the last chapter in the bottom pane, and jump between them simply by clicking the mouse – or by tapping F6 to cycle between the two panes, the ribbon, and the status bar. It’s the same deal in Excel.
Word even lets you change the zoom level in the two panes, as shown in the above image, so that you can view multiple pages in one pane while viewing full-size text in the other. You can split the window by dragging the splitter bar at the very top of the vertical scroll bar, or by opening the View tab on the Ribbon and selecting Split (or Remove Split to restore a single window). Excel has a similar split-window feature but unfortunately doesn’t let you choose different zoom levels in each pane.
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 customise 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.”
Use a named range in a formula in Excel
One major convenience of a named range is that you can use it in 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.
Convert an Excel formula result into static text
Here’s a simple tip that can save a lot of manual labour. I often want to keep the results of formulas or calculations, and I want to discard the original data that went into the results. For example, I 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 I want to delete the now-redundant material in my original two columns.
If I merely deleted the original two columns, the results in the new column would disappear with it, so I 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.
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.
Friends don’t let friends enter bad data in a worksheet
One guaranteed way to mess up a worksheet is to enter the wrong kind of data in a cell – for example, text instead of numbers, or a decimal instead of a whole number. Prevent yourself – or anyone else who uses your worksheets – from entering the wrong type of data by using Excel’s Data Validation feature. In a table, select the cells that should only contain one kind of data. On the Table Tools tab, click Data Validation and specify the kind of data that can go into the cells. How do you alert the user who tries to enter the wrong data? Read on.
Tell your friends exactly what kind of data to enter
The same dialog in which you specify the kind of data that can be entered (see above) has a tab labelled Error Alert. Fill in the title and message that you want to pop up when the wrong kind of data gets entered. You can also select the icon for the message by selecting the Style dropdown.
Line up multiple sheets in the same worksheet for inspection
In the above image, note the box at the upper right. How did I get two sheets from the same worksheet to appear on the same screen? Simple. On the View tab, I clicked New Window, 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.
Conditional formatting with negative numbers in Excel
In Excel 2010 you can apply one of the pre-built colour-coded conditional formatting options to data that includes negative numbers (not only positive numbers, as in Microsoft Office 2007). 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 set this up this kind of conditional formatting by clicking Home, Conditional Formatting, Data Bars, and then choosing a colour set in the Gradient Fill gallery. The results are visible in the screen shot above in column P.
Display the actual cell values when creating or editing a formula
The previous tip shows how to display formulas in the entire spreadsheet. 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 above shows a formula that normally displays the address D12:O12, but when I selected that address and pressed F9, the actual values appeared.
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, I was originally in cell D35 and pressed Ctrl-[. This highlighted D12, D26, and D35; and D12 became the current cell.
Highlight the formulas that reference the current cell
The previous tip explained how to use Ctrl-[ (Ctrl-open-square-bracket) to see all the cells referenced by a formula. What if you want to do the reverse, and see the formulas that reference a cell? Select the cell, and press Ctrl-] (Ctrl-close-square-bracket). As in the previous tip, the selection moves to the first formula that references the cell. Press Enter repeatedly to navigate to the other formulas that reference the cell. In the screen shot, I was originally in cell D3. I pressed Ctrl-]. This highlighted B3, D12, and D35, and B3 became the current cell.
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.
SEE ALSO: 20 Excel tips for creating stylish spreadsheets
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 dialog will be applied 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.
Also Read:
- 10 Excel Data Entry Tips Everyone Should Know
- 10 Helpful Excel PivotTable Tips for Quick and Efficient Data Analysis
- Complete List of Excel Keyboard Shortcuts By Category
Courtesy : itproportal
I am no longer positive the place you’re getting your information, but good topic.
I must spend some time learning more or working out more.
Thank you for magnificent info I was on the lookout for this info
for my mission.
Hi. Thanks for your very educating pages.
I just want to let you know that two of the three links you have under Also Read are incorrect; the 1st points to the 2nd, and the 2nd points to the 3rd. The 3rd seems to be correct. Also, none of your images show on my Win 10 computer using SRWare Iron, which is a Chrome derivative.
Thanks again, and Cheers.
Aw, this was a really good post. Taking the time and actual effort to create a great
article… but what can I say… I put things off a lot and never
seem to get anything done.