7 handy Excel tips
MS-Office Excel (2013, 2010 and 2007)
Excel contains many features that can save you a lot of work. But you just have to find them. We put some sophisticated Excel tips for you here. To be used in Excel 2013, 2010 and 2007.
All columns in the correct width
If you make Excel calculations, the outcome sometimes does not fit into a cell. You'll then see '######' instead of a number. Adjust the width of the cell as follows:
• Move the mouse pointer to the correct column until you reach the above letters. Then move right until you reach the column separator. You see the mouse pointer change into a black cross with horizontal arrows.
• Double-click the column separator and the width is adjusted to fit the number exactly.
Note: If you select the entire worksheet and then double-click on any column separator, the width of all columns will be adjusted. You select the worksheet in its entirety by using the keyboard shortcut Ctrl + A.
You have a set of numbers in Excel. Now you want to add up a few of those numbers (or the whole series) and quickly see the outcome. By default, Excel has a convenient option in the status bar. Betting that you have not noticed yet? But try the following:
• Select a few numbers in Excel.
• Look at the bottom right of the status bar. You will see the average, how much numbers are selected and the result of the sum. Handy or not?
Note: Right-click anywhere on the status bar. You will see that you can make visible next to 'Average', 'Number' and 'Sum' also the "Minimum", "Maximum" and "Numerical count.The difference between "Numerical Count" and "Number" is that the latter has all the selected cells (including text) cells, where it first counts the cells with a numerical value only.
Longer text in a cell
Sometimes you have a piece of text, such as a title in a table that is on the long side. Unnecessary to pass this text across the entire width: divide the text across multiple lines into one cell. This is simple: type the text and on the spot where you want to break (always) press Alt + Enter, and then type on. At the end of the text, press Enter. As you can see, the cell is not wider but only longer so that the text is visible in its entirety.
If you create a table with titles, you want to keep seeing it when you scroll down. Therefore, lock the titles!
• In the Ribbon, click the View tab.
• Click freeze and select the appropriate option.
You can choose from:
1. Freeze Panes
2. Freeze Top Row
3. Freeze First Column
Option 2 and 3 are most useful: with option 2, the upper row will be visible, and with option 3, the first column.
Use the function key F4 to repeat the last operation. This can save you a lot of time. Suppose you change the text color of one cell. Click the following cell, and then press F4. Now the text color of this cell is also modified.
If you know someone's birth date, you can show the current age in Excel. Type the following exactly into Excel:
• Type the date of birth (e.g., 30-08-1960) in cell A1.
• Type in cell A2 as follows: =INT((TODAY()-A1)/365)
• Press Enter.
Deleting duplicate values from a table is an easy task in Excel:
• Select the column that presumably contains double values.
• Click the Data tab.
• Click Remove Duplicates .
• Click OK.
• You will then see a window indicating how many duplicate values have been removed. Click OK.