I recently ran an Excel Intermediate course with delegates of varying experience and requirements; nothing unusual in that. Several of the delegates said that they "used Excel all day every day". Luckily, I always start an Intermediate course with a simple recap exercise so that I can gauge delegates' knowledge and ensure that I cover the right content and pitch at the correct level.
The first recap exercise involved working out what one might spend shopping if one bought X number of items at Y price each – a simple multiplication.
Several delegates put their hands up and said they did not know how to enter a calculation in Excel. I asked one of them who used Excel "all day every day" what she did to find out the answer. She said she worked it out on her calculator and typed the answer into the spreadsheet. "Well", I said "You have a great big calculator in front of you in Excel!"
But this lack of knowledge of the basic building blocks of Excel is not particularly surprising. Nowadays it is possible to make use of Excel for analysing large amounts of data without knowing how to enter calculations. Features such as sorting, filtering and even basic pivot tables and pivot charts can be created from options on the ribbon or shortcut menu (right mouse click). There is a lot of useful stuff you can do in Excel without knowing how to enter a formula. But imagine how much more you could do if you knew that basic building block. And just imagine how many more functions for analysis and reporting you could gain access to.
So, as many of you will know, a formula starts with the equal sign and then you can type or select the cells that contain the values you want Excel to calculate with. So in our stationery purchase example, we would start with the first item which is in row 2 and we want the answer under the COST heading which is column D. So we can enter the formula with these simple steps:
- Select D2, where we want the answer
- Type = (equal sign) to start the formula.
- The first value we want to use is the PRICE for Pencils in B2 so type or select this cell.
- In computers, the asterisk (*) is the multiplication symbol, so type this
- Then type or select the next cell C2 which has the QTY for Pencils.
- This is the end of the formula so press Enter or click on the Enter icon – the tick in the formula bar
The power of this formula is that if the PRICE or QTY values change then the result immediately recalculates. And you can Copy and Paste or Fill the formula to other cells to repeat the relationship.
If this lack of knowledge strikes a chord, then get in touch with us to discuss how SJM Training can help you expand your knowledge of the basic building blocks in Excel.
