Excel is a powerhouse of tools, and with 2007 and 2010 there are some amazing new features like 3-D modelling, but one of its most valuable tools are the formulas, and they have been around since its inception.
But don’t despair. With Excel 2007 came a few new ones that have brought calculations that used to be the domain of the true ‘Excel Guru’ to the average user.
Time spent doing some Excel training to understand some of the basic formulas (and I don’t mean SUM or COUNT) will be time well spent. Learn how you can use the top 10 Excel formulas to draw valuable insights from your data and free up hours of your time.
And with loads of free Microsoft Excel online training available, there’s really no excuse.
1)
IFStatement – this is possibly the most over-used formula, and for good reason. It’s one of the most versatile formulas. In English it reads “If this criteria is met, do this, otherwise do that”. On its own the IF formula doesn’t seem all that special but combine it with other formulas and its power is life changing (well, in your Excel world anyway).
IFStatement – this is possibly the most over-used formula, and for good reason. It’s one of the most versatile formulas. In English it reads “If this criteria is met, do this, otherwise do that”. On its own the IF formula doesn’t seem all that special but combine it with other formulas and its power is life changing (well, in your Excel world anyway).
2) SUMIF/S – the SUMIF is a variation of the IF statement and as it suggests, it sums data if it meets a specified criteria. Use it to sum only figures from a list that meet your criteria. For example; only SUM the values that are <$100. The SUMIF can perform this calculation before you can blink, summing thousands, or hundreds of thousands of rows of data. New in Excel 2007 is the SUMIFS (plural) and as the name suggests it allows you to set more than one criterion. e.g. Sum all amounts <$100 that were in January.
3) COUNTIF/S – is the sibling of SUMIF/S but it’s slightly simpler. With COUNTIF you can instruct Excel to only count cells if they meet a specific criterion, or for multiple criteria you can use the COUNTIFS formula.
4) VLOOKUP – this formula will find ‘a needle in a 100 acre haystack’. It looks within a vertical list of data, hence the V, and finds the value in a column to the right of the result. I liken it to looking at a menu. First you look at the meals (say they’re in column 1), then you glance across to the price column (in column 2). You could tell VLOOKUP to find the Chicken Tikka Masala and tell you how much it is J VLOOKUP’s sibling, the HLOOKUP works the same but for horizontal lists of data.
5) AND – the AND formula, much like the IF statement really needs to be teamed up with other formulas to get its real power. One of the most common teams is the IF and AND formulas. With the IF statement we said in English it would read “If this criteria is met, do this, otherwise do that”, but team it up with the AND formula and it can read “If this criteria is met, AND that criteria is met, do this, otherwise do that”.
6) OR – like the AND formula, on its own is nothing fancy, but team it up with the IF statement and you get “If this criteria is met, OR that criteria is met, do this, otherwise do that”.
7) CONCATENATE – a big word, but it’s really simple. CONCATENATE simply allows you to join values from one or more cells together into one cell. As an alternative you can also use the simpler and shorter ampersand, ‘&’, as it essentially does the same thing but without the need to type a really long word. For example; say you have text in cell A3 and text in cell F1 that you want to merge into one cell. You can simply enter the ‘&’ formula like this =A3&F1. To add a space between the text simply add an extra &” “ to the formula like this =A3&” “&F1. Where the “ “ adds the space.
8) PROPER – This formula changes the formatting of any text so that each word begins with a Capital Letter.
9) LOWER – Similar to PROPER, LOWER as you might imagine, changes all the text from whatever case, to all lower case.
10) UPPER – Related to PROPER and LOWER, UPPER formats all your text in UPPER CASE.
If you’d like to master Excel and unleash some of its power a good place to start is with these formulas. This will also give you the confidence to tackle some of, what may seem like, the more complex formulas that will really boost your productivity.
Free Microsoft Excel online Training is available in various levels of quality, and with most people being visual, video tutorials are by far the most popular.
Bio: Mynda Treacy is an accountant and co-founder of My Online Training Hub. We believe that learning how to use Excel, Word & Outlook will save you money, and training should be quick, easy and free. Visit our website to get instant access to over 5 hours of free Microsoft Excel video training plus 2.5hrs of Word training and 3 hours of Outlook training.
No comments:
Post a Comment