Which version of Excel to use for a Naturally Sales training course

Neil Shorney

I've been asked a number of recently which version of Excel people should have before attending one of our courses, so for this week's Excel tip, we'll do a quick comparison of the versions. Over the past 12 months, I've come across people using Excel 2003, 2007 and 2010. Now we have 2013 being released so there's another version in the mix. Each version is an improvement on the previous version and contains various enhancements and bug fixes, but in real terms, what's important? Excel 2007 represented a big change over previous versions. Not only did the interface chance, but there was a huge increase in spreadsheet size and format. Some people are still on 2003 and it's not a problem, but there are some pretty major changes. The number of allowable rows increased from 65536 to 1048576 in 2007 and 2010. This was a useful change when working with large data sets. Other key changes were the introduction of the menu ribbon, a new pivot table interface, and a change in file format from .xls to .xlsx. These were big changes. Since then, changes have been confined to scientific functions which we don't use in day-to-day life, and bug fixes. 2010 gave a fresher look to the ribbon and layout of 2007, and some bugs were fixed. Two useful additions were the right-click Paste values option (a great time-saver) as well as the ability to select ranges from other sheets to create data validation drop-down lists. And here is an important consideration when some employees use 2007 and 2010. You may know that in Excel 2007 data validation, it was possible to define a list from a different sheet by typing the range reference manually even though it wasn't possible to select it with the mouse. In 2010, you can select this range with the mouse. However, if a 2010 workbook is opened with 2007, any data validation references to other sheets are forgotten. Even if the original list was defined manually in 2007. I've had a quick look at the trial version of Excel 2013 and the overall structure appears to be similar, but with another face-lifted view and a cleaner look. It's also more tablet-friendly. There have been improvements in auto-fill with the new Flash fill function which predicts your patterns. Charts and pivot tables have been improved. So back to the original question... if you're attending an Excel training course, it really doesn't matter whether you're on 2007, 2010 or 2013. Even using 2003, a good instructor should be able to deal with the differences. It comes more down to preference. I much prefer 2010 over 2007 for the reasons stated above, and I expect to upgrade to 2013 fairly quickly. But that's personal choice rather than business necessity. The key is not to get hung up on the version, and think about how you communicate with Excel to get the business results you need. For more info on Excel training from Naturally Sales, visit www.nsales.co.uk/microsoft_excel

Some useful data presentation functions

By Neil Shorney

Here are two useful functions plus a light-hearted one...

The first is great for anyone dealing with manually-entered data which might be a little untidy. The TRIM function will remove any preceding or training spaces. You could create a new column after a column of data (for example, names or addresses) and enter the following formula: =TRIM(cell ref). For example if A1 contains " Hello ", and you enter =TRIM(A1), the result will be "Hello". This is a little step towards cleaner data.

You're probably familiar with using MAX and MIN to display the largest and smallest values in a list. But what if you want the third smallest? We can use SMALL to do this. Imagine D1:D5 contains 8, 5, 6, 1, 3 and you want to know the third smallest number. Use =SMALL(D1:D5,3) to show this.

A fun one to close: try entering =ROMAN(2012) into Excel and see what you get...

SUMIF and COUNTIF

By Neil Shorney

If required, Excel is able to add or count only records which agree with a statement. These formulas are quick-and-easy and structured as follows:

=SUMIF(range,criteria,sumrange)
=SUMIF(B2:B9,"Printer",D2:D10)

SUMIF will sum a column if the corresponding cell in another column is true. COUNTIF will count the number of records which are true.

=COUNTIF(range,criteria)
=COUNTIF(B2:B9,”Laptop”)