Need to send out a standardised form to dozens of your customers? Hundreds? Thousands? Feeling the cramp in your fingers from typing out the same form over and over? Even good old copy and paste ain’t enough?

From the interconnectivity of Microsoft’s Word and Excel comes an amazing step in office automation – the Mail Merge.

What you are going to learn here will make you WORSHIPPED.

Let’s start with some data.

This is a list of a 12 name customer database. You will see that we have 5 columns of information – ‘Salutation’, ‘Name’, ‘Address’, ‘Membership Points’ and ‘Bonus Points Remaining’.

How are we going to create a standardised form with all this data without using copy and paste 60 times?

Step 1: Ensure that all your data columns have TITLES. This is essential when using the Mail Merge. Save your data in an Excel file. I have called mine ‘Mail Merge’. Remember that all your data is in Sheet 1 – you will see why later.

Step 2:

Here we have a standardised template for mailing to your customers – just use Word and type anything you want, really!

Now, all you have to do is select where you want to place the fields. Click on the spot you want to place the data field and then select ‘Insert Merge Field’ and click on the data field you want! Easy! Notice that as you place the merge fields, their names appear framed by <<>>, like <<Address>> and <<Salutation>>.

Step 9:

Once you are done, click on ‘Preview Results’. See that the data we have from the ‘Mail Merge’ Excel file has been instantly transferred to the positions we wanted onto the Word document? Are those tears in your eyes?

Step 10:

Next to the ‘Preview Results’ button, there are some arrows. Click on the single arrow facing right. Notice that the number in that little box on the left of the arrow has changed from ‘1’ to ‘2’.

Notice also that the information in the Word document has now changed from the details of the first customer in the ‘Mail Merge’ Excel file to the second customer? You can select which customer’s details appear on the Word document by scrolling left and right in this manner.

Step 11: You can print these documents one by one if you wish, but there is another way to make it much faster.

Step 12:

Select the ‘Finish and Merge’ button and select ‘Print Documents’.

Step 13:

An option box will appear with 3 options. ‘All’ will print all the records in your Excel file, ‘Current Record’ will print only the one you have on your screen now and ‘From; To’ will allow you to specify the specific range of records, maybe 1 to 3 or 10 to 12.

Tip:

You might like to see all the documents for a proof read before you print. You can do this by selecting the ‘Finish and Merge’ button and clicking on the ‘Edit Individual Documents’. You will see the same option box in Step 13, but instead of printing the records selected, Word will display them all for your inspection.

Do you need to get the breakdown of sums, averages or a count of your data? Need to get them by SEPARATE CATEGORIES? Needed it yesterday?

This series of formulas has so many uses, it’s amazing. You’ll be able to get the sums, counts and averages of items by specifying criteria of your design!

Let’s start with some data. Here we have a list of students, with the following columns – Name, Age, School, Grade and Fee. Now, let’s see what we can do.

SUMIF/SUMIFS

This function adds values together if they fulfil certain conditions that YOU specify!

SUMIF will only add values based on 1 specified condition

SUMIFS will add values based on more than 1 specified condition

Here’s an example. Let’s try to find out how much in fees students from school X pay:

=SUMIF(C:C,”X”,E:E)

Like all formulas, begin with ‘=’ and then the function SUMIF, with the contents framed by ‘(‘ and ‘)’.

How about the contents?

C:C refers to the data that contains the condition or criteria that would decide how the data is added together. In this case, this is the school the students go to.

“X”, is the criteria that we have specified. Hence, we are looking at column C:C and specifying “X” as the criteria. As it is a letter, remember to frame X with “ and “. If it was a number, there is no need of “ and “. So, we are looking only at students that go to school “X”.

E:E refers to the data we want added together, in this case, the fees that each student pays.

Hence, we want to add all the school fees together of all students that go to school “X”. So, we are adding the values contained in E:E that have the condition “X” in column C:C. In this case, the answer is $150.

SUMIFS

How about SUMIFS?

This function is very similar to SUMIF. Let’s see if we can find out how much fees students in school X that have B grades pay:

=SUMIFS(E:E,C:C,”X”, D:D,”B”)

Note how this is slightly different from SUMIF.

Like all formulas, begin with ‘=’ and then the function SUMIF, with the contents framed by ‘(‘ and ‘)’.

E:E refers to the data we want added together, in this case, the fees that each student pays.

C:C refers to the schools that the students have gone to. By specifying “X”, we are stating that we want only students from school “X”.

D:D refers to the grades that the students obtained and by specifying “B”, we are stating that we only want students with “B” grades.

Hence, we will get from the formula, the fees that all students from school “X” with “B” grades pay. In this case, $100.

COUNTIF/COUNTIFS

Like SUMIF and SUMIFS, COUNTIF counts values with 1 specified condition, while COUNTIFS counts values with more than 1 specified condition.

COUNTIF

Let’s see if we can find the number of students who attend school “Y”.

Here’s how it works:

=COUNTIF(C:C,”Y”)

C:C refers to the list where we want items counted. In this case, C:C refers to the schools that students go to.

“Y” refers to the item we want counted. This means that we want the number of students who attend school “Y”, the answer being 3.

COUNTIFS

Let’s see if we can find the number of students that attend school “X” and have gotten “B” grades:

=COUNTIFS(C:C,”X”,D:D,”B”)

Now, C:C and D:D refer to the lists where we want items counted, with “X” being the school attended and “B” being the attained grades.

Hence, we will get the number of students who have attended school “X” and attained the “B” grade, which is 2.

AVERAGEIF/AVERAGEIFS

Much the like the previous 2 functions, AVERAGEIF determines the average of items with 1 specified condition, while AVERAGEIFS, like its counterparts, determines the average of items with more than 1 specified conditions. They work in the same way too.

AVERAGEIF

Let’s find out the average of fees paid by students from school “Z”:

=AVERAGEIF(C:C,”Z”,E:E)

C:C refers to the list of schools, with “Z” being the condition specified. So we are looking at school “Z” specifically.

E:E refers to the items we want averaged, in this instance the fees paid. So we will get the average of fees paid by all students from school “Z”. The average being $58.33.

AVERAGEIFS

The final function we will look at today is the plural version of AVERAGEIF. AVERAGEIFS determines the average of items with more than 1 specified condition.

Let’s try to find out the average of fees paid by students from school “Y”, who have gotten “D” grades:

=AVERAGEIFS(E:E,C:C,”Y”,D:D,”D”)

E:E refers to the items we want averaged, in this instance the fees paid.

C:C refers to the list of schools, with “Y” being the condition specified. So we are looking at school “Y” specifically.

D:D refers to the grades that the students obtained and by specifying “D”, we are stating that we only want students with “D” grades.

With all that, we get the average of fees paid by all students from school “Y” who have attained a “D” grade. The result of that is $25.

Like this:

We all work with data – tonnes of it. Ever wanted to find all the information you need, with just 1 click of the mouse?

Let’s build an INTERACTIVE DATABASE.

Time to learn: 30 minutes

Functions you will need:

Data Validation

Vlookup

Conditional Formatting

Step 1:

Let’s start with some data.

Here is a list of suspects in a database. All of them have a name and a ‘Status’, whether they are ‘Wanted’, ‘Acquitted’ or ‘Missing’. They also have a column next to their ‘Status’, indicating the ‘Action’ to be taken if they are seen – to ‘Apprehend’, ‘Issue Warning’ or just to ‘Inform Police’.

In the field named ‘Source’, click and drag on the list of cells with the names of all the suspects, from cells A2 to A13.

You will see that the list of names now has a dotted line bordering it and the ‘Source’ field now has the reference ‘=$A$2:$A$13’. Click ‘OK’.

Step 5:

Now you have a dropdown list of all the names of suspects in our database!

Remember that the dropdown list is presented in EXACTLY the same order as they are in the original list (cells A2 to A13). You can alter the order of the dropdown list by altering the order in the original list (cells A2 to A13).

Step 6:

Now, type in ‘Name’, ‘Status’ and ‘Action’ as shown.

These will be OUR data titles.

Step 7:

In cell F2, under the title ‘Status’, enter the following VLOOKUP function:

=VLOOKUP(E2,A2:B13,2,FALSE)

E2 refers to the cell that the names of the suspects will appear in from our dropdown list.

A2:B13 is the complete cell reference of all the suspects’ ‘Name’ and ’Status’.

2 refers to the option that we have specified –that we want the information in the 2nd column to the right of the suspect’s name, counting the suspect’s name as well (meaning ‘Name’ being the 1st column and ‘Status’ being the 2nd column), to appear in the cell we have currently selected – F2.

Step 8:

Do the same in the cell under ‘Action’ –cell G2:

=VLOOKUP(E2,A2:C13,3,FALSE)

E2 refers to the cell that the names of the suspects will appear in from our dropdown list.

A2:C13 is the complete cell reference of all the suspects’ ‘Name’, ’Status’ and ’Action’ .

3 refers to the option that we have specified –that we want the information in the 3rd column to the right of the suspect’s name, counting the suspect’s name as well (meaning ‘Name’ being the 1st column, ‘Status’ being the 2nd column and ‘Action’ being the 3rd column), to appear in the cell we have currently selected – G2.

Step 9:

Now, when we select any name from the dropdown list in E2, their ‘Status’ and ‘Action’ are automatically displayed next to their names!

Step 10:

Let’s make it even more sophisticated!

Click on cell F2. While still ensuring that the cell F2 is selected, select the ‘Home’ tab and select ‘Conditional Formatting’ then ‘Highlight Cells Rules’ and then ‘Text that Contains…’.

Step 11:

An option box will appear.

In the ‘Format cells that contain the text:’ field, enter the text ‘Wanted’. In the box to the right of that, let’s just select Light Red Fill with Dark Red Text. Click ‘OK’.

Step 12:

Follow step 10 again and this time, in the ‘Format cells that contain the text:’ field, enter the text ‘Missing’. In the box to the right of that, let’s just select Yellow Fill with Dark Yellow Text. Click ‘OK’.

Step 13:

Follow step 10 again and this time, in the ‘Format cells that contain the text:’ field, enter the text ‘Acquitted’. In the box to the right of that, let’s just select Green Fill with Dark Green Text. Click ‘OK’.

Step 14:

NOW, when you select the name ‘Gary’, notice that the ‘Status’ of ‘Acquitted’ is now coloured in green? You have just formatted the cell F2 to respond to the different ‘Status’ types by changing colour! Great job!

Notice the change now when you select the name ‘Wendy’. Cool, right?

Tip:

You can see how many formats you have programmed into the cell by selecting the cell and then by selecting the ‘Home’ tab, ‘Conditional Formatting’ and then ‘Manage Rules’.

Notice that we have programmed 3 rules into cell F2 that change the colour of the cell depending on whether the text in it is ‘Wanted’, ‘Missing’ or ‘Acquitted’.

Ever wish you could tell at a glance who your top performers are? Now you can!

Conditional formatting is an exciting aspect of Excel that allows you to change the way your spreadsheet LOOKS, based on the DATA entered.

Let’s have a look by starting with some data:

Here we have 12 colleagues and the total number of calls they have made from January to June.

Now, we will colour-code all the numbers to find out the top 3 and the bottom 3 in terms of performance each month.

Step 1: Select the ‘Home’ tab and highlight all the values in the ‘Jan’ column, like so.

Step 2: Click on ‘Conditional Formatting’ and then select ‘Top/Bottom Rules’ and then ‘Top 10 items…’. Remember to ensure that all the values in the ‘Jan’ column are still highlighted.

Step 3: An option box will appear. In the field that indicates ‘Format cells that rank in the TOP:’, enter the value 3. This will mean that the conditional formatting will affect only the top 3 numbers. If you enter 5, it will then affect the top 5.

Step 4: You can select how you want the cells to be formatted by selecting one of the options in the dropdown box on the right. Here, we will choose ‘Light Red Fill with Dark Red Text’.

Have tonnes of data that you can’t make sense of? Need a fast way to organise and view your 10,000 cell spreadsheet? Then, pull that rabbit out of your hat with pivot tables!

A pivot table compiles and summarises all the information in a spreadsheet into a table that you can manipulate easily. Saves you time and makes you look incredible!

The pivot table is an awesome function and helps you do many things at once!

Step 1:

Let’s start with some data.

Here we have 19 colleagues and the number of sales they have completed from January to March.

Now, what if we want to count the total sales for all of them per month?

Step 2:

Now, click on any cell that has our data in it (meaning anywhere from cells A1 to D20) and then click on the ‘Insert’ tab and select ‘Pivot Table’.

Step 3:

An option box will appear. Now, notice that in the box below ‘Select a table or range’, you will see the cell reference Sheet1!$A$1:$D$20.

Notice that this is exactly the cell reference of our list of names and sales per month from January to March?

This means that the pivot table will use all the information from cells A1 to D20. Neat!

Tip:

You can change this reference by selecting a new range of cells if you want. This allows for changing the contents of your table.

Step 4:

Now, we move to the option ‘Choose where you want the PivotTable to be placed’.

You have 2 options.

Option 1:

‘New Worksheet’ automatically creates a new tab with the pivot table once you click ‘OK’, like so.

Notice that a new sheet, ‘Sheet 4’, has been created with the pivot table on the left side of the screen and the options on the right side of the screen.

Option 2:

‘Existing Worksheet’ allows you to place the pivot table on the same sheet as your data. In this case, we have selected cell F2, which becomes bordered in dotted lines.

In the ‘Location’ box, you now see the cell reference Sheet1!$F$2, which refers to the location where we want the pivot table placed – in cell F2!

Clicking ‘OK’ creates a pivot table on the same sheet, with several options boxes on the right side.

Step 5:

Now, notice that the titles of the data (the first cell of each column), ‘Name’, ‘Jan’, ‘Feb’ and ‘Mar’ are now listed in the box ‘Choose fields to add to report’.

Step 6:

Click and drag the field ‘Name’ into the ‘Row Labels’ box.

Notice that the field ‘Name’ is now in the ‘Row Labels’ box and that a list of names has been created, from Andrew to Shirlin.

Tip:

By clicking on the dropdown arrow you can actually manipulate the list by sorting it in ascending order using ‘Sort A to Z’ or descending order using ‘Sort Z to A’ or even take out some names by unchecking the arrows next to each name.

Step 7:

Now, click and drag all the other field names from ‘Jan’ to ‘Mar’ into the ‘Values’ box.

Done! Now you have a table with the grand total of all the sales made for each month from January to March.

Tip 1:

You can change how the data is processed. Say, you want to count the total number of instances instead of adding them all up.

Here we have a pivot table displaying the calls and deals closed for several colleagues:

Step 1:

To change the way the data is displayed, go to the menu at the bottom right.

Step 2:

By left clicking on any of the fields under ‘Values’, you will see this menu.

Select ‘Value Field Settings…’.

Now, a whole list of options will appear for you to select on whether you want your data summed, counted or even averaged!

Step 3:

Let’s just select ‘Count’ for illustration.

Notice that the data for calls made are no longer summed? What we have done is COUNTED the instances where numbers appear for each of the colleagues – that means that we have counted the number of times any calls have been made at all.

Tip 2:

Did you know that pivot tables add up values corresponding to repeated names?

Consider this database. Notice that the names in yellow are repeats of the names above? Name for name.

Applying a normal pivot table’s procedures to the data, the pivot table displays the combined values added up, WITH NO REPEATED NAMES!

This means that the pivot table knows when a name is repeated and instead of just reproducing the data, adds it to a matching existing name. Saves you tonnes of time!

Like this:

Want to know how to make your spreadsheet look like it’s a thinking being? Looking for a way to make your data talk back to you?

The most interesting aspect about Excel is the ability to execute logical equations or FUNCTIONS.

IF

One of my favourite functions is the IF function. Remember that this is a formula and as such, whenever you type it into a cell, you have to begin with a ‘=’ sign.

The structure of the IF function has 3 parts and looks like this:

=if(1. a logical or mathematical expression; usually referring to another cell, like if cell A1 is equal to 3 or if cell A1 is equal to cell B1, 2.then what you would like this cell to say or do if the expression indicated in 1. is true, 3. Lastly, what you would like this cell to say or do if the expression in 1. is false)

Here’s an example:

=if(A1=3,1,0)

What I have done is create an IF function that checks if cell A1 is equal to 3. If it is equal to 3, the cell the formula is in then gives you a value of 1. If cell A1 is any other value besides 3, the cell then gives you a value of 0. So, what right?

Like so many things in Excel, the IF function is so, so much more!

Here’s another example:

=IF(A1=3,”I do not want a 3!”,”Not a 3 is fine.”)

Notice that the text in the IF function is framed by “ ”. All text in any function – if you want it to behave like text, must be framed by “ ”. What we have done is create a function that checks whether cell A1 is equal to 3. If cell A1 is equal to 3, the cell then gives you the text, “I do not want a 3!”.

If cell A1 is any other value except 3, the cell then gives you the text, ”Not a 3 is fine.”

This makes for wonderfully interactive formulas that not only do the work, but add personality!

Again, so what? Really?

Here’s another one. Let’s say you have 1,000 random numbers ranging from 0 to 9999 and you wanted to know which ones were between 13 and 54.

Here’s how we’d do it:

=IF(AND(A1<=54,A1>=13),”Between 13 and 54!”,”Out of range.”)

Whoa, what the hell was that? The IF function allows a combination with 2 other types of functions, AND and OR. We’ll talk about that in a moment.

So the formula above says that if cell A1 is less than or equal 54, indicated by the <= AND if cell A1 is ALSO more than or equal to 13, indicated by the >=, then the cell tells you, “Between 13 and 54!”. If cell A1 is outside of this magical 13 -54 range, the cell then says, “Out of range.”

The best part is that by placing your mouse pointer at the LOWER RIGHT corner of a cell, then holding the left-button, you can move the mouse pointer downwards, thereby COPYING the formula onto the cells below. Dragging upwards copies the cell contents upwards too. You will notice that in the cell along the same row as A9, the formula does not mention cell A1 anymore, but A9.

Neat, huh?

AND

The AND function is used in combination with the IF function to add more logical expressions to the first one.

So, the example:

=IF(AND(A1<=54,A1>=13), “Between 13 and 54!”,”Out of range.”)

Means that BOTH the conditions A1<=54 and A1>=13 MUST be fulfilled before the text “Between 13 and 54!” appears. Otherwise, “Out of range.” appears instead.

OR

The OR function is similar to the AND function, but as you probably guess, you sly fox you, when used in combination with the IF function, assesses whether EITHER of the logical expressions is fulfilled before returning the values if the expressions are true.

Here’s an example:

=IF(OR(A2=1,B2=1),”Team 1 in Season 1 or 2″,”Team 1 absent”)

Let’s take the columns A and B as a team roster for Seasons 1 and 2. The expression using IF and OR above checks whether Team 1 is in EITHER Season 1 or 2, giving you the text, “Team 1 in Season 1 or 2″. If Team 1 is absent from both seasons, the text, “Team 1 absent” presents itself.

Now, note the difference if we had used IF and AND instead:

=IF(AND(A2=1,B2=1),”Team 1 in Season 1 or 2″,”Team 1 absent”)

Remember that the IF and AND function when used together, checks if BOTH expressions are true before returning the text, “Team 1 in Season 1 or 2″. Since Team 1 is only in either Season 1 or 2 but never both at the same time, “Team 1 absent” is returned. This happens because, yes, BOTH conditions, that Team 1 would be in Season 1 and 2 at the same time, were not met.

I hope this gives you a good illustration of how the AND and OR functions work and their differences. Did I mention that you can use IF, AND and OR at the same time? Okay, okay, another time.

Here’s something extra:

Let’s say you have 9 teams, named 1 through 9 and you want to group teams 1 to 3 into a separate team, called Team Badger, teams 4 to 6 into another team, called Team Skunk and teams 5 to 9 into a final team called Team Dead. How would we do that? Here’s how: