About Us

We’re a small bookkeeping company in Bolton, Lancashire. We offer a large range of services from simple clerical work such as purchase ledger to help creating a credit control policy or managing bad debt.

Some of the more sophisticated work we offer is installing and managing Sage Line 50 as well as staff training. If you need more detailed reporting to help you manage your company, we can do that as well whether its costing BOM or more detailed client or product analysis. Check out our blog pages on Excel to get an idea of what a management report looks like that will actually help you manage your business.

Maybe you already have good people handling your books but you’d like someone to finish off the month end and report on your company’s status. Give us a call.

 

Advertisements

Excel Basic Calculations

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!

Example 5

Now the headings are much clearer.

Example 1

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.

Example 2

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.

=SUM(D2:D19)

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.

Example 3

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.

Example 4

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.

Basics of Data in Excel

The five sections of the Data Ribbon are:
Get External data; Connections; Sort & Filter; Data Tools; Outline.

Excel Data Ribbon

The first two sections are about fetching or updating data from elsewhere other than the worksheet that is in focus. This can be from another spreadsheet, MS Access, an SQL database or Sage amongst others.

The next section allows the user to sort data in two different ways.

Excel Data 1

In the above image, each of the green titled sections have a symbol on the right hand side. This allows the user to select criteria within long lists of data.
At the top of the selection box is a simple smallest to largest and largest to smallest selection. Beneath that are filters for colour and text. At the bottom is the filter for contents within the chosen column.

In the above instance the choices are All, anything with a V or an X or Blank cells in that column. The selection that has been made is to view all rows of data with an X in that column or where that column is blank. So, the column on the right is only showing rows with an X or where the cell is empty.

To clear a selection and view the whole list, select All.

Excel Data 3

When the Sort button in the Data Ribbon Bar is selected, the choice is about which column is defining the sort criteria. Note that in the top right of the criteria box there is the option to choose whether or not to use the data’s headers as criteria. If the box is not selected the columns will be listed, otherwise the list of columns will appear similarly to the above example with the headers being the choice.

There is an option to search for a particular piece of text or a value. Separately hit each of these keys:

Alt then E then F

This brings up a search box. Enter a value or some text and Excel will search for that data within the open worksheet.

Excel Data 4
In the example above, a range of cells was selected (from the 682 to the 11,984). The criteria was 93.6 and the button ‘Find All’ was selected. This lists all instances of 93.6 within the selected range. Note that selecting one of the instances in the list will send focus there and the formula bar shows each of the values added in that cell. Note also that hovering the mouse over the cell shows the comments (which in this instance are a list of the clients and invoice numbers that were received on that day and in the order in which they appear in the formula bar)
.
If no cell range is selected, the search will search all of the worksheet. There is a danger that two cells are selected by accident. This means that only those cells will be searched. Selecting a single cell searches the whole sheet.

Searching in a spreadsheet for a value such as 93.6 will produce hundreds of results. If that seems likely, the search should be from an appropriate range. If the search is for 298.36 or some other value that is likely to be unique, it is appropriate to search the whole sheet by selecting a single cell.

Formatting in Excel

The image below is the left half of a standard Ribbon in Excel. The Ribbon bar is broken up into sections. Clipboard, Font, Alignment, Number, Styles, Cells and Editing for the Home Ribbon.

Excel Formatting

The Number section is probably the significantly different section from other Home Ribbons. The default options are Currency (never use this – its pretty but distracting format wise), Percentage, Currency without signage (a comma and two decimal places) and the option to increase or decrease the number of decimal places visible.

Underneath the Ribbon bar on the left is the Name Box. Generally, that shows the name of any field or object that is in focus. In the above it shows L9 which means the focus cell is 12 columns over from the left and nine down from the top. Ranges of cells and individual cells can be given names. If focus is one of those, the name of that cell or range of Cells will appear in the Name Box. If focus is on a Pivot Table or Chart, the name of that object will appear in there. The name can be overwritten in that box.

To the right of the Name box are one or three symbols: the Function symbol (read as function of x in maths and is the symbol visible in the image above) is a quick access key to all of the formulae that Excel can use. If a formula is being written in a cell a cross and tick symbol appear. These are: delete the formula and accept the formula as written at present.

To the right of the function symbols is the Formula Bar. Any data or text added to a cell will appear in here. With more complex formulas and long text strings, it is often easier to edit from within the Formula Bar

Excel Formatting 2

The above is an image of the right hand side of the Ribbon Bar.
Styles are useful for some of the more esoteric functions in Excel as well as to change all formatting within a range of cells.

The Cells section does most things that right click does.

The Editing section allows some basic editing and Data Sorting as well as insertion and deletion of cells, columns and rows.

Entering Text or Data in Excel

To Enter data in a spreadsheet field, select the required field using the mouse, Tab key, Arrow key or Enter key as appropriate and begin typing.

Excel distinguishes between data (text) such as names and numbers and formulas. Formulas are recognised by Excel with the first character in a cell being either a + or an =. So, entering  James 25 leaves James 25 as the visible product of that cell’s editing. Entering 25+13 leaves the visible product as 12+13. This is not a set of numbers Excel can use later in a formula. Excel will treat that data as text.

If the entry in a cell is 25, the visible product is 25 but Excel will presume that this is a formula (or number) and can use that data in other functions. So, if there is a column with three cells of data; 15, James, +25+13, Excel will ignore James when using that datum in a calculation that has included all three cells. So, =Sum(A1:A3) will produce the visible product 40.

 

Excel does not use trailing zeros beyond the decimal point. So the value 1,000.00 should simply be entered as 1000. There is no need for the comma, decimal place or penny zeros. Entering 1000.50 should be 1000.5.

Historically, it was not possible to have more than 255 characters in a cell which limited the size of formulas Excel could manage. So, attempting to add large numbers of numbers in a single cell could be problematic. To this end, experienced Excel users tend to enter only the data Excel requires. Obviously, this also save time in data entry.

Moving Around Excel

It is possible to move around a worksheet using different keys.

Most people use the arrow key and some people use the mouse. The other options are the Tab key and the Enter key.

Each key actually has a different effect.

The arrow keys simply move the focus around the sheet. An individual press of an arrow key will move the focus up, down, left or right from the field that was in focus. This is useful if you are processing in a small area of a few fields but is not recommended when entering more than one row of data.

If an arrow key is used to move focus to the next field required and then the Enter key is used, the focus will move down one row but not change columns. So, if several rows of similar data is being entered (such as in the EPC Register), arrowing across to enter data in each field and then pressing Enter at the end of the row brings focus to the field below the last column and not the first column where the next row of data will be added.

It is possible to use the mouse to change focus. The problems with using the mouse are varied. It is possible to re-create focus on the field and accidentally overwrite the data previously entered; it is distracting to move from the keyboard to the mouse and back again.

Better to focus on the data input accuracy and use the mouse as infrequently as possible.

If the mouse is double clicked in a cell, focus moves to the point in the cell where the cursor is. So, if you click in a cell with +12+1315 at some point in the middle and then select another cell, Excel will prepare to put the new cell’s reference into the formula that was selected at the point within that formula that was selected.

Excel data entry 1

In the example, if the cell is double clicked and then cell reference D17 is selected, the formula changes to +12+D171315 but the about (i.e. what is visible) becomes 12. Double click should only be used to edit formulas in the formula bar. Using the mouse to change fields in data entry is not recommended.

The Enter key will move the focus down one row from the last aggregated focus. So, using the arrow keys or mouse to move focus from field to field and then pressing Enter means that the aggregate focus was only one field.

When entering data along rows with multiple columns (such as the EPC Register), pressing the Tab key to move along the row maintains aggregate focus from the first field to the last. Pressing Enter at the end of a row having used Tab to move along the row puts focus on the first column but one row down. (i.e. the beginning of the next row).This makes data entry much more convenient. To go back one column using Tab, press and hold the Tab key and then hit the Shift key.

An Excel Overview

Excel can look a bit daunting when you first encounter it but the basics are very straightforward.

Excel basic image

Across the top is a quick access menu. The image at the end is a drop down menu where you can add or remove the items you use the most.

Underneath that is the ribbon bar. This is where all the menu items are to be found. It is split into two parts: the top part has text such as ‘File’ and ‘Home’; Underneath are the icons which activate the functions. Except that in the example above, ‘File’ is green which means that menu is active. If you clicked on ‘File’ a drop down menu shows things you can do with that relate to filing your document.

By default, you do not need to  click on ‘Home’. The icons in the main ribbon at present relate to ‘Home’. If you clicked on ‘Insert’, you get these icons:

Excel Insert Ribbon

Each text item offers a new menu. Have a play. Notice that when your mouse is anywhere within the ribbon, that scrolling with the mouse wheel changes the menu items, so you can scroll from ‘File’ to ‘Review’ just by rolling your mouse’s wheel.

The next level down of the menu is the Name Bar and the Formula Bar. The Name Bar has ‘A1’ in the example above. That lets you know that the part of the work area that you are is in Column A and Row 1. To the right is the Formula Bar. The text in there is ‘=1+3’. This means that in the bit of the Excel work area that is active (i.e. where you are), Excel has named ‘A1’ and it knows that what is in that area is a formula; notice that in A1, there is the number 4. That is of course, the resolution of the formula =1+3.

A quick note to end today, you can just type numbers or letters such as 4 or Jim into the active area but if you want Excel to recognise that you’re typing a formula, you must start with = or +.