So, what’s the point of Excel? Its sat there on your computer but you never use it! Today, I thought we might have a look at how to use Excel to do a basic budget. We’ll expand this analysis in the coming posts to look in more detail at your outgoings. Remember, you can use all of these posts to do the same sort of things at work.
First we’re going to build a list of our outgoings. I’ve created a set of headings in the cells in the first Row under Columns A, B, C & D. This is not just good practice in that it gives you a guide but is required when we start to look at some of the funkier functions in Excel.
Then I populated the list with some imaginary outgoings. If the formatting is going a bit wonky, have a look at my Formatting in Excel post.
Speaking of formatting, notice that I took A1 to D1 and made the font white and the background black. to do this, select the upside down triangle next to the bucket symbol in the Fonts bit of the Ribbon bar and change the color to Black and select the upside down triangle next to the A underlined in red and select white!
Now the headings are much clearer.
The strength of Excel is its ability to do calculations quickly and easily.
In our report, the only thing we might want to calculate at first is the total
of all the expenditure (Cost – in column D). Excel offers several ways to do it. The most obvious being to select the bottom of the data to be added (that’s cell D20) and click on Auto sum on the right hand side of the Ribbon bar.
The keyboard shortcut is select the data to be added and hit the Alt and =
at the same time. To do this, click in D20, hold the Alt key down (next to the space bar) and the equal sign (next to the Backspace button). Then select D2 with your mouse and drag down to D19 (the end of the list of values in the ‘Cost’ column). Hit enter.
The manual way of doing it is to write out the Sum equation.
Notice that the top most item is in cell D2 and that the lowest item is in cell D19.
So, the way to read +SUM(D2:D19) is:
add up =Sum
everything which has been selected i.e. everything between these style of brackets ( ) and that everything is from D2 through to [ : ] D19.
You must start a formula with a + or = otherwise Excel thinks it is text and if you’re typing the formula manually, if your selecting a range of values, you select the first item then type a colon : then the last item in your range. Don’t forget to bracket your range!
Regardless of what you may have been told in elementary school, Sum means Add and
Excel likes to use Sum.
The brackets ( XXXX ) define what needs to be actioned (in this case, the action called Sum).
There should be no gaps.
So now the cell D20 has the value.
Notice, as we looked at in a previous post that underneath the word ‘Font’ and next to the ‘Function X’ ( ƒx ) is our formula =SUM(D2:D19).
There are other ways to add things up.
If you wanted to add up two numbers such as 50 and 28, you must bear in mind the syntax we have been using. So, any formula must start with a + or an = . After that, it can be just like school. I’ve made the fonts green for the example above.
So, typing =50+28 in any cell, will give a value of 78. Equally, you can say =F2+H2. You can also use =Sum(F2:H2) . Notice though that you have selected a range of cells that also includes the cell G2 and if you enter a value in G2, Excel will add all three numbers.
You can also subtract: +50-28 gives 22 and +F2-H2 gives 22
Multiplication works in exactly the same way. The symbol for multiply is * (which you can find at the top of the number pad on the right of your keyboard or as the Cap for 8 at the top of your alphabetic keys).
So, 50 times 10 is +50*10 or +F2*F4
Division is identical except the symbol for divide is / +50/10 or =F2/F4
You can sum a whole column such as column D: Sum(D:D). However, this can be wasteful of computer resources. Obviously, the active cell (where you are entering the formula) should not be in column D or Excel will try to add the total to itself.
We’ll look at other things Excel can do with your data in another post. Notice though that for many of these basic functions, we often start with a database (otherwise known as a List) and manipulate the data that populates it.