Microsoft Excel – Top 10 Power Formulas

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).

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.

Microsoft Excel Wakeup Call – True Story

For 8 years my job involved working in Excel all day. I didn’t need to start it up each morning because I’d put it in my Windows Start Up folder so it automatically opened when I logged on each day.  I was working as an accountant in an Investment Bank in London and Excel was our

most important program.

I was the Excel Guru in our office.  My colleagues came to me for help but I’d never had any formal Excel training; I just picked things up on the job and learnt a trick or two from my colleagues over the years.  I believed I was an expert.  I could handle what I thought were advanced features like Filters, Pivot Tables, Charts and I could even record the odd Macro when pushed. I was fast too, I used my keyboard shortcuts so much I wore out the letters ‘C’, ‘V’, ‘X’ and ‘Z’.  I could use VLOOKUP’s, IF statements, COUNT and more. What else was there to know?

Then we upgraded from Microsoft Office 2003 to 2007 I went on a Microsoft Office Online Training Course.  Because there were so many changes from Microsoft Office 2003 to 2007 I thought it could be useful. Ideally it was online training so it wasn’t expensive and I could watch the tutorials when it suited me and pause if I needed to attend to something urgent. 

The training blew my mind.  It became clear that I only knew the tip of the iceberg.  Even though there were a lot of new features in Excel 2007, I discovered tips and tricks that were there in earlier versions and I’d been missing out all along.

It was exciting to learn all these new tricks that would save me hours of work, but I was sorry that I’d wasted all those years doing things the long way around when there were shortcuts available all along.  It meant I could cut down the time some of my regular tasks took me by half, and I learnt features and techniques that I could use to present my reports in ways that would get the message across fast.  

I’d spent all those years with powerful software at my fingertips but I didn’t know how I could use it to do my job better and save time.  It was a classic case of ‘I didn’t know what I didn’t know’.

If you’ve never had any formal training, and you use Microsoft Office programs in your day to day job then you will definitely benefit from doing some training. Don’t kick yourself like I did for spending hours of your day doing things the long way around.

Online training is very affordable compared to the classroom based courses.  Plus you get the benefit of being able to watch the videos at a time that suits your workload, pause, play, and rewind when you need to 24/7.

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 10 hours of free Microsoft Office video training including Excel Training, Word Training and Outlook Training.

Top 10 Outlook 2007 Productivity Tips You Must Know

Microsoft Outlook 2007 is full of easy to use tools that will power-charge your productivity. And with plenty of free Microsoft Office online training available, there’s really no excuse.

1) Signatures – stop typing your name and contact details on every email. Simply set up one or more signatures and have them automatically appear on every email. You can format them and add in images which looks professional, and saves you time.

2) Quick Parts – These are predefined objects, including images, or text that you can insert into an email with a few clicks of your mouse. Use them for answering FAQ’s or any other information you find you type out over and over again.

3) Folders – Reduce clutter in your inbox by organising your messages into folders. Be careful not to go overboard with the number of folders you set up as this may mean you spend too much time debating with yourself over which folder to put the message into. Instead use the advanced search tool in Outlook to quickly find what you’re after.

4) Rules – You can set up rules to have Outlook automatically colour code emails based on who they’re from, who they’re sent to, or even what the subject is. Rules can automatically file your emails into folders, or even better, forward emails to someone else for action without having to lift a finger.

5) Search Folders – Do you file your messages by task, but sometimes do a search to find all messages relating to a particular client? If
you find you’re performing the same search regularly you can save time by setting up a search folder, then when you want to do the search simply open the folder and it updates automatically with one click.

6) Get Organised – Categories allow you to group like messages so you can search, sort and find all messages that are related to a particular category. For example, you might have a category for a project or job, or even a client. You can set up categories any way you choose. Flags allow you to set reminders to revisit a message or level of importance. You can use flags and categories to quickly sort and filter your emails.

7) Tasks – Tasks are an electronic To-Do list. You can assign tasks to others, set reminders and keep track of progress.

8) Recurring Events – Never forget a birthday or anniversary again. Set a recurring even in your Outlook calendar for recurring events that occur regularly. And give yourself time to take action by setting your reminder in advance.

9) Collaborate – Have someone else manage your calendar by delegating permission to view and or edit you calendar.

10) Journal – The journal allows you to collate information about a task or contact by appending documents like Word and Excel files. Plus keep track of communication with a contact like email, phone calls and more.

If you’re serious about improving your productivity these Microsoft Outlook tools are a must learn.Free Microsoft Outlook Training is available in various levels of quality, and with most people being visual, video tutorials are by far the easiest to follow.

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 10 hours of free Microsoft Office video tutorials including Microsoft Excel, Word and Outlook.

Excel Pivot Tables And Why You’d Use Them

Excel Pivot Tables can make quick work of analysing huge amounts of data. At first they may seem complicated and only for the ‘Advanced Excel User’, but in reality they’re actually quite simple.

In fact they’re great for people less confident in Excel, because you can’t break anything, as the Pivot Table only reads the underlying data, it doesn’t actually modify it.

Pivot Tables allow you to take columns of data and summarise and change the orientation of it. For example, you can take data that’s in columns and ‘Pivot’ it so that you have a grid of headings spanning both columns and rows with your data summarised in the body of the table.

In doing so, you can quickly get answers to questions, and meaningful information about the underlying data. Imagine you have a year’s worth of sales data (1000’s of rows) laid out with the following column headings:

• Date

• Product

• Region

• Salesperson

• Units

• Sale Amount $

And you might like to have a report that answers the following questions:

• Total $ Sales per month, by product, by salesperson.

• Count of product sales, by region, per month

• Count of sales by product, by salesperson

• Total $ Sales by region, by product, per week

• Average Sales by region, per month

The permutations are almost endless, as are the benefits. One of the great features is how easy Pivot Tables are to change, taking seconds to alter, so you can build them by trial and error, build multiple tables from the same set of data, all with varied and useful information.

Sure, you can achieve similar analysis with formulas but Pivot Tables are a better solution. They’re faster to build, easier to update, easier to change, require less memory.

As a bonus you can also create Pivot Charts from the same set of data. Update the Pivot Table, and the chart updates too.

The most important requirement before you can build an Excel Pivot Table is to have your data laid out in columns, with each column having a unique heading, and no blank columns in your table of data. It’s also preferred that your data is sorted and there are no blank cells.

There are plenty of Excel Pivot Table Tutorials available, and for anyone who works with data that suits a Pivot Table, it’s worth taking the time to learn how to use them, as it will be time well spent.

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 10 hours of free Microsoft Office video training  including Microsoft Excel, Word and Outlook.