MS Excel Tips & Tricks

Whilst using Excel and sorting through all the data on your spreadsheet you may have thought about using COUNT and COUNTA functions.

However many people do not know the difference between the two. They both count what is found in the cells.

• The COUNTA function will count cells that contain text (labels) and also cells that contain numbers (values).
• The COUNT function will only count cells that contain numbers.
• Blank cells are not counted by either COUNT or the COUNTA function.

• Want to create a chart? Select the data and press F11
• If you need to highlight a whole block make sure your active cell is somewhere in the block and then press Ctrl+Shift+8
• Have you used split panes in your workbook? Flick between them quickly by pressing F6, go to the previous pane by pressing Shift+F6
• Bored of clicking on the Sheet tabs? Just press Ctrl+ Page Down to switch to the next worksheet and Ctrl+ Page Up for the previous worksheet.
• Sometimes you need an extra worksheet before your current worksheet, to get one just use keys Shift+F11.
• You may find that after putting border lines around all of your tables you do not want them there. Highlight the cells and press Ctrl + Shift + _.
• Need to Number format your cells with two decimal places? Press Ctrl + Shift + !.
• Many people use Excel to organise their finances, to quickly add the currency format to a cell in your spreadsheet press Ctrl + Shift + $.
• To change your cells to percentages press Ctrl + Shift + %.
• People who don't use a mouse can select an entire row by holding Shift + Spacebar. They can select an entire column but holding Ctrl + Spacebar.
• Repeat your last action by pressing F4.
• Don't like the look of some columns or rows? Don't worry you can simply hide either, rows by pressing Ctrl + 9 or columns by pressing Ctrl + 0. Unhide them by pressing the same buttons, however holding Shift too.

People who are sorting out their own financial affairs or businesses trying to sort out theirs may need to know the last day of the month for a given date.

As Excel knows people want to do this they have created a formula to make it easy for them to achieve this.

If you have a column of dates, use this formula to find the end of month for each day by using the fill handle.

The formula is as follows:

=DATE(YEAR(C5),MONTH(C5)+1,1)-1

This example assumes that the first date in question is in cell C5, so the formulas can be placed in any other cell, such as D5

Finding the largest and smallest number in a set of data by hand can get very confusing. If there is lots of data to sort through you may forget what you are looking for before you even get to the end.

Two of Excel's most common functions are the MAX and MIN functions. These display the largest (MAX) or smallest (MIN) value in a series.

• If you want to find the maximum: type =MAX(cell range) in to a cell and press ENTER.
• To find the minimum: type =MIN(cell range) in to a cell and press ENTER.

However what if you need the 2nd or 3rd largest or smallest values instead of the largest or smallest?

• The formula =LARGE(cell range, n) returns the nth largest value of a series.
• The formula =SMALL(cell range, n) returns the nth smallest values of a series.

In both functions, 'n' represents the ordinal of the number you want to display. For example putting in 2 for n will give you the second highest number.

One of the formulas in Excel's arsenal is the MODE() function. This will display the most frequently occuring value in a particular range of cells.

• The function looks like this: MODE(cell range)

Cell ranges are written with the smallest cell number followed by the largest, with a semi-colon in the middle e.g. (B2:F8).

Therefore, if 35 is the most commonly recurring number in the thirty cells between B2 and F8, then the function will display 35.

Constants make calculations easier to read so worksheets are more easily understood. Constant values also need to be given relevant and memorable names. It is also easier to change the value of a constant, as opposed to numerous identical values throughout a worksheet.

For example, instead of entering 17.5% in each cell when you generate a VAT amount you could name a constant VAT and assign a value of 0.175 to it.

• From the menu, select Insert —> Name —> Define.
• In the 'Names in Workbook' line enter the constant's name as VAT (no spaces allowed).
• In the 'Refers To' box enter the constant's value as 0.175 - making sure any other text has been deleted.
• Click OK

In Excel, you may have cell B10 containing a net amount, and in C10 you want to work out the VAT on the amount.

• In cell C10 you would enter the formula =B10*VAT. This will give you just the amount of tax payable on the amount in cell B10.
• If you want to work out the gross amount, that is the total amount including VAT, enter =B10+(B10*VAT) or =B10*(1+VAT), both produce the same value.

When people create large spreadsheets they may not want to spend their time typing in tricky formulas that never seem to do what they want to do.

Excel has decided to give us a toolbar button, which when pressed gives you some basic functions.

It looks like a crazy E (actually an uppercase letter sigma '∑') and when pressed will give you a choice of SUM, AVERAGE, COUNT NUMBERS, MIN AND MAX.

You click on the option you want and it automatically does the sums for you.

It can be very difficult to work out a person's age when they just give you the year they were born. It's easy when they were born at the beginning of each decade, however subtracting or adding years can get confusing.

You do not have to worry anymore as Excel has created a function, which will work it out for you.

It's not just a person's age, it will figure out any amount of time between two dates, even down to the number of days.

The DATEIF() function in Excel calculates the number of days, months, or years between two dates.

To work out someone's age, type their date of birth in cell A1. Make sure you use slashes (/) to separate day, month and year. Then in cell A2, type =DATEDIF(A1,TODAY(),"y") and then press ENTER.

TODAY() automatically inserts today's date. "y" specifies that only the number of years should be shown.