A Few More Excel Basics...

Written by James Bland
Group Marketing Manager
The Sundial Group, Highgate House

Having looked at manipulating text-based information in the last Excel post; names, addresses and suchlike; this time I want to quickly introduce a few numbers into the mix…


Bringing Order to Chaos

So your booking/sales/CRM system will export orders/sales/enquiries in a big list with dates attached, but you’re currently ploughing through that manually to obtain monthly totals.

You’ll get more than the data below, of course, but for the sake of this example let’s say your columns go as such…

• A: Customer Name

• B: Value of Order

• C: Date of Order

So long as the dates are properly formatted and Excel recognises them as such, using the formula =MONTH(D1) with return, numerically, the month of the year in which the date in Cell C1 falls. Repeat down the column to begin your categorisation. When complete, use the expression =COUNTIF(D1:D100,1) to count the number of orders placed in January, month 1. Change the number in the second part of the expression to view for February, March etc.

Your “COUNTIF” function though doesn’t have to just work with numbers. You could count all of the people named “James Bland” in your customer list by simply changing the column it looks at from D to A, and then changing the number 1 to “James Bland” (include the speech marks – that’s quite important when ever you want to look at text within a formula instead of numbers)

Show me the money…!

Having obtained in column E the numbers corresponding to the months in column D, you can then acquire your monthly order totals. Use the expression =SUMIF(E1:E100,”=1”,B1:B100) to do this. In the first segment you are telling the formula to look in column E, where your monthly classification numbers reside. In the second segment, you are telling it only to find orders where the month is January (=1) – increasing this number will interrogate your other months. In the third segment, you tell it to take the order values from Column B.

Your “SUMIF” function can again count the value of orders that “James Bland” has placed. Again, change the column initially interrogated from E to A, and then change “=1” to = “James Bland” (again, remember to include the speech marks). Your column B data will have to be numerical though – Excel cannot add letters to numbers!

A Little Pointer

It’s a bit manual this one, but let’s say you’re the sort of person who likes writing telephone numbers without spaces. Or maybe you need them like that to upload them into a SMS transmission system. When you type a string of numbers into Excel, it quite rightly thinks it’s a number, so it suppresses leading zeros – hence the telephone number 01604731850 becomes 1,604,731,850.

The difficulty with Excel is that when you begin to overtype a cell, the previous contents disappear instantly, meaning you have to remember the number that was there initially. One way is to repeat it back to yourself so it stays in your immediate memory, but eventually your co-workers will get annoyed, or think you strange, or both.

A quick way to sort this out is to go into the cell and just type an apostrophe in front of the number, ‘01604731850. The apostrophe tells Excel that whatever follows is to be treated as text. This does mean, however, that you now cannot add two telephone numbers together, but why on earth would you want to…?