On the heels of our recent series on automation and productivity hacks, it occurred to me that some of you might have to do spreadsheets more often than I do. And let me tell you, when I do them, it’s a project. In an effort to help us all, I went in search of the most helpful Excel hacks to make data entry and calculation easier. Here are five gems I bookmarked for my own use.
Tip #1 Entering values into multiple cells using CTRL + ENTER
Scenario 1 – quickly enter data in multiple cells
- To quickly enter data in multiple cells, first select all of the cells. Type in the data and press CTRL + ENTER, which enters the same value in all of the selected cells. Select all the cells you want to fill.
- Type ‘John’ then enter the same value in all selected cells using CTRL + ENTER. For non-contiguous cells, hold down the CTRL key and click to select each cell.
- To enter values in non-contiguous cells first hold down the CTRL key and click to select each cell then type in ‘John’ and use CTRL + ENTER to enter the same value in all selected cells.
Scenario 2 – quickly correct data in multiple cells
- To quickly correct the data in multiple cells, select all of the cells containing the names you want to fix. Let’s say you need to replace ‘Betty’ with ‘Britney’ in five cells.
- How do you quickly replace ‘Betty’ with ‘Britney’ in these five cells? First, select all five cells containing ‘Betty’. For non-contiguous cells, hold down the CTRL key and click to select each cell. You can then type ‘Britney’ and press CTRL + ENTER, which will enter ‘Britney’ in all of the selected cells.
Scenario 3 – quickly fill upwards
- If you would like to fill up instead of fill down, you can do this by a clever modification of the CTRL + ENTER trick. Let’s say you want to replace ‘Britney’ with ‘Charlie.’ There’s already a ‘Charlie’ entered in the cell below. Select the cells starting with ‘Charlie’ and ending with the topmost ‘Britney.’
- How do you fill ‘Charlie’ upwards to overwrite the five cells showing ‘Britney’? With the cells selected, press F2 to go into Edit mode, then press CTRL + ENTER to copy the correct name into all of the selected cells.
- With the cells selected, press F2 then press CTRL + Enter and ‘Charlie’ has been filled upwards to replace ‘Britney.’ Note, the first cell with ‘Charlie’ must be the active cell. You can see this in the screenshot as it has a white background instead of a blue background.
Tip #2 Autocomplete using the tab key
Here’s a tip that saves you time if you need to enter data that’s already been placed somewhere in the column above. Let’s say you have entered ‘John’ on row 1. When you down one cell to row 2, start typing ‘J.’ Excel automatically enters the full name ‘John’ and highlights the ‘ohn’ black so you can accept or reject Excel’s suggestion.
Excel will complete only those entries that have text or text with numbers. It doesn’t look for numbers, dates or times. The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entry.
Excel autocompletes data based on existing entries in that column. After Excel completes what you started typing, you have different options:
- Press TAB to accept the entry and move one cell right.
- Press ENTER to accept the entry and move one cell down.
- Continue typing to enter a different value.
- Press BACKSPACE to delete the automatically entered characters.
If you have several names beginning with ‘J’ then you need to type a few more characters until Excel can suggest the correct value. For instance, if you have already entered ‘John’, ‘Jess’ and ‘James’ in one column and want to enter ‘John’ then you need to type ‘Jo’ and Excel will then fill in the rest of the name.
Tip #3 Taking a screenshot in Excel
You probably already know how to include an entire screen shot within your Excel worksheet (Press ‘PrintScreen’ and paste the screen shot where you want it with Ctrl+V). However, if you only want to show a selected region like a small part of the screen in your spreadsheet, try this:
- Select the area in your Excel sheet.
- If you have Excel 2007, go to ‘Paste’, then ‘As Picture’, then ‘Copy as Picture.’
- If you have Excel 2010, go to the ‘Copy’ link and click ‘Copy as Picture.’
Once the screen is placed in your workbook, you’ll be able to move it in the same way as you can any other graphic. Remember that adding graphics to the worksheet will increase the size of the file in which the workbook is saved.
Tip #4 Embedding an Excel sheet in a Word document
You’ll need to know this in case you have to create reports for presentations and want to include an Excel sheet for reference. It’s no trouble at all simply to embed the worksheet directly in your documents – and this is how you do it.
- Select the tables in the Excel sheet which you want to embed in your Word document.
- Copy it to the clipboard by pressing Ctrl+C.
- Now go to your Word document and click the cursor where you want the chart to be placed.
- Go to the Paste button in your toolbar and click ‘Paste Special.’
- In the dialogue box select the Microsoft Office Excel Worksheet Object (2007) or Microsoft Excel Worksheet Object (2010) as the method of pasting.
- Ensure that the ‘Paste Link’ radio button is selected and click OK to insert the linked chart into your document.
Tip #5 Trapping an error message
If you’ve worked with formulas, you’ve probably encountered the dreaded formula error; rather than returning a numeric result, the formula cell displays a strange message such as #VALUE! or #DIV/0!.
Most of the time, this means you need to track down the source of the error and fix it. But sometimes a formula error simply means that the data used by the formula is not yet available. For example, say you run a small telemarketing company. You might have a spreadsheet set up to track your daily sales as a percentage of calls made.
- The formulas in column D do the calculations that come up with the percentages. For example, cell D4 contains the formula =C4/B4.The answer was then converted to percentages by using the percent style option.
- The formula does its job well – as long as there is data to calculate. An empty cell (such as B9) is treated as a zero, and division by zero is not allowed. As a result, Excel displays an ugly #DIV/0! error message, which makes your entire worksheet look like it was created by a novice.
- You can avoid displaying formula errors by re-writing your formula to use an IFError function. For example: =IFERROR((C4/B4),””)
- Displays a blank cell if the division operation results in an error (cell B4 is empty or contains 0), yet still displays valid results.
- If you prefer, you can replace the empty string (“”) with other text of your choice – just make sure the text is enclosed in quote marks.
I hope you found these hacks as time-saving as I did. Now what Excel hints have you discovered that we can add to this roster?
Courtesy: intuit.com