Select your data. Press F11 on the keyboard and voila! An instant column chart is created on a new worksheet…
Transpose Data
This would be useful if you wanted to change the orientation of the display of your data, but really didn’t want to retype it all. Copy all the original data first, find an empty area and click on a cell where you want to begin the copy. Go to Home > Paste > Transpose (you […]
Keep Preceding Noughts
When an input value starts with zero Excel will delete the zero(s) by default – try adding a single quote mark ahead of the first zero.
Navigating Sheets
It’s common knowledge and instinctive to click on a different sheet tab in a workbook to activate it. You can also use the keyboard; press Ctrl-Page Up and Ctrl-Page Down to go up and down the sheets. If your workbook contains a large number of sheets they may not all fit on the screen and it […]
Split full names in single column
How to split full names (in a single column) out into separate columns. Use functions: =LEFT(B4,FIND(” “,B4,1)-1) =RIGHT(B4,LEN(B4)-FIND(” “,B4,1))
Formula Syntax
Sometimes you might find a quick reminder of the formula syntax useful when working in Excel. If this is the case then type an equal sign followed by the function name and then Ctrl+Shift+A. For Example typing =Sumif and then using the keyboard stroke Ctrl+Shift+A will give you: =Sumif(range,criteria,sum_range). In 2010 you will find Excel […]
Annotations in your formulas
Say six months ago you wrote a formula similar to this: =10000+787-12*299.64. Now you find you just can’t remember what exactly each figure represents. Try writing yourself a reminder about the calculation and add a hidden note to your formula by using the N() function i.e. =10000+787-12*299.64+N(“Loan+Interest-12 months of loan repayments”). The N() function converts […]
Quick Drop-down List in Excel
Do you want to repeat data that’s already been used in the column? To enter Yellow in a new cell in the column Excel will bring it up anyway when simply typing Y – this is because there is only one data entry starting with the letter Y. Conversely if you need to enter Blueish, […]
Quickest way to review a Sum or Average
A quick way to find the sum and average of cells in Excel 2010 is to select adjacent or non-adjacent cells and look in the status bar. Excel displays the average of the numbers, a count of the cells, and the sum.
Keyboard strokes for AutoSum
The AutoSum tool is already fast, but even quicker just try selecting a cell at the bottom of a column of figures and press Alt+=, which inserts the =Sum() function (just like AutoSum does).