Excel can do some really great things like **SUM** and **AUTOSUM, SORT** and **FILTER,** and some other clever functions that you may not be using like **CONCATENATE** or **VLOOKUP?** I’ve put together a list of 10 really useful functions that I think you should get to know…

**
CONCATENATE** (combining data in columns): Let’s say you’ve been given a spreadsheet with a list of names in it. The names are in two separate columns called Firstname and Lastname, but you want to combine these to make one column called Name.

The formula for the example below would be **=CONCATENATE(A2,“ ”,B2)**. The space between the quotation marks gives you the space between the first name and last name in the results.

Want to see it in action? Watch the Joining data from multiple cells (4m 18s) video on Lynda.

**TEXT TO COLUMNS** (splitting data in columns): This time your spreadsheet has just one column called Name, but you want be able to sort by Lastname so you need to separate them out into Firstname and Lastname.

To do this select the column you wish to split, go to **>Data >Text to Columns >Delimited >Next** then tick **>Space** (for example) **>Finish**.

See how it works in the Splitting cell data into multiple cells (2m 22s) video in Lynda.

**AUTOSUM:** this function is probably the most widely used, but did you know you can use it horizontally at the same time as vertically? Just select the data you want to add as well as the column and row you want the results to appear in, then choose **>AUTOSUM** and it will add the results instantly.

The keyboard shortcut for AUTOSUM is **Alt + equal sign (=)**.

Watch the Adding a whole worksheet (1m 48s) and Adding numbers using Sum and AutoSum (6m 11s) videos in Lynda.

**COUNT:** want to know how many items you have in a column? To do this you use the **COUNT** formula, and if you delete a value from the column it will recalculate the number of items automatically.

The formula for the example below is **=COUNT(B3:B8)**. Use **AUTOFILL** to extend the results across columns – hover over the corner of the cell until you see a plus sign (+) and then drag to the right.

Want to see **COUNT** in action? Watch the Working with numbers in columns (4m 53s) video in Lynda.

**AVERAGE:** if you want to know the average value of a group of numbers, the formula for the example below

is:** =AVERAGE(B3:B8)**. press **Ctrl + enter** to get results and stay in the same cell.

Want to see **AVERAGE** in action? Watch the Working with numbers in columns (4m 53s) video in Lynda.

**IF STATEMENTS:** will output one result if the statement is true and output a different result if the statement is false. The general syntax for this is: **=IF(statement, value_if_true, value_if_false).**

The formula for the example below is: **IF(B3>=500,$G$4,$G,$3)**. To get the **ABSOLUTE REFERENCES** (eg $G$4) use and to display the results as %, select the column then go to **>Format Cells > Number >Percentage > no decimal places**.

Need to see this step-by-step? Check out the Using IF (4m 49s) video in Lynda. Want more? Have a look at Using SUMIF and AVERAGEIF (4m 15s) in Lynda.

**CONDITIONAL FORMATTING:** lets you format numbers and dates according to the value. Often this is used to highlight high or low values but it can also be used to highlight values within a range.

To use the automatic conditional formatting options, select the range of numbers you want to format **> Conditional Formatting** (from the ribbon) **>choose your selection from the dropdown menu eg Colour Scales or Data Bars**.

To learn more, check out the Using Conditional Formatting (4m 6s) and Using Custom Conditional Formatting (5m 49s) videos in Lynda.

**DATA SORTING & FILTERING**: one of the most useful Database functions in Excel. If you haven’t got to grips with Sort & Filter now’s the time to learn – they’re some of the most powerful functions in the Excel arsenal.

To sort a column, select the **Column header >Sort & Filter > then either Sort A to Z or Sort Z to A**. In the example below, I sorted highest to lowest ie **Sort Z to A**.

If you choose **>Sort & Filter >Filter** you’ll notice Excel adds drop-down menus to each of your column headers – this allows you to perform multiple sorts on more than one column.

To see Sort & Filter in action, check out the Basic and multi-field sorting (6m 30s) video in Lynda.

**PIVOT TABLES**: are really useful when you have a lot of data to analyse and it’s not obvious how they relate to each other. For the example below I selected my data then chose **>Insert >Tables >Pivot Tables >New Worksheet (Sheet3) >OK**.

Excel will create the shell of your Pivot Table, you then need to drag and drop the Fields into the appropriate areas. In the example below I put **Month into Filters**; **Region into Rows**; **Size into Columns**; and **Sum of Quantity into Values**.

The Month is automatically set at **All,** but I can filter by a different month quickly and easily to change my results.

Want to know more? Have a look at the Creating a basic Pivot Table (2m 46s) video in Lynda.

**LOOKUP FUNCTIONS:** when you have a lot of data in a worksheet, VLOOKUP (vertical) which is for data arrange down columns and HLOOKUP (horizontal) which is for data arranged across rows, can help you extract specific data.

The general syntax for this is:

Lookup value = which row do we want?

Data range = the entire data area (all of the columns)

Column # = the column to match

True = approximate match

False = exact match

I want to look up the Product Code and have it return the exact Description. The formula for this is: **=VLOOKUP(A7,A6:F14,FALSE)**

To see how VLOOKUP works, watch the Creating look up tables (3m 6s) video in Lynda.

All of these functions are more can be viewed in the Excel 2010 Essential Training (6h 21m) video in Lynda. Good luck and happy spreadsheeting!

## Leave a Reply