Excel Assessment – The Fundamentals

All formulas should start with an equals sign: =A1*20.  You can use also use a + sign, although this isn’t normal practise.

The key for saving a workbook is CTRL and S. This is a good shortcut to learn immediately as it is a good idea to save your work on a regular basis in case your computer crashes unexpectedly. This allows you to save your work without having to leave the keyboard to use the mouse.

The hashes appear when the column isn’t wide enough to display the contents of the cell.

Fill the some cells with a blue fill colour: The cell fill tool is on the Home Tab.

Insert an image which contains the company Logo: The insert picture tool used for this in on the Insert tab.

Change the page size to A4: You would use the Page size setting on the Page Layout tab for this.

Set the Zoom to 100%: The zoom controls are in the View Tab – although it is actually quicker to use the controls in the bottom right hand corner of Excel.

The quickest way to copy the contents of a cell to other cells below it is to double click the Fill Handle – you would have scored 5 points if you selected this answer.  This only works when there is data in a column next to the column you are working in, and it will only copy down as far as the data in a column next to it.

You can also click and drag the cell handle down – but that is not as quick – you would get 2 points if you chose that option.

The answer to this question is A.  When you right click this area, a window will open up with a list of all your sheets – you can then quickly move through the list and select the one you want.

If you know the game “Battleships” you will immediately understand the address of a cell – it is the intersection of the column letter and by the row number.  In this case, the activated cell is D5.  If you refer to the image above, the address is also displayed in the name box just above cell A1.

The address of a range of cells is made up of two individual cell addresses – the cell in the top left hand corner, and the cell in the bottom right hand corner.  The top addresses are joined with a colon (:) – so the address of the selected cells in this question is C3:F8.

The correct answer is the Filter.  By using a filter on your information, you can quickly hide information showing you just the information you want to see.  For example if you have a list of sales for the year, you could use a filter to see just the sales of a specific customer without deleting the rest of the information.

The tool being used in this image is the Format Painter.  It is a quick way of applying formatting you have used on one cell to other cells.  Tip: If you need to use the paint brush on many different cells, double click the Format Painter icon and it will stay “active” until you click the icon again, or press the ESC key.

Information on the selected cells is displayed in the status bar at the bottom of the Excel window – information here can show you the sum, count, average and can be configured to show you many other mathematical information. This is a quick way of knowing the sum of a range of cells in your sheet without having to type a formula or use a calculator.

When a formula contains the address of another cell, it normally stores this address as a relative reference – using an example – you can refer to the house next to you as the “house next door” – when you move house, the “house next door” is now a different house – it moves with you, so to speak.  Excel formulas work the same way – for example, if you entered this formula as =E4*E1 – when you copy the formula to the cell below, the formula will change to =E4*E2, so it no longer points to the cell that contains the commission.

The solution to this problem is to use an absolute reference – using the example of the house, this would be like it’s address – “107 Kirkbride Road” – no matter where you move, “107 Kirkbride Road” still points to the same house.  The way you do this in Excel is use $ signs in the cell address.  So the correct answer to this question is =E4*$E$1 or =E4*E$1.  When you place a $ before the column letter, this freezes the column, but not the row – which means if you copy the formula to cells on the left or to the right, it will still point to column E, but if you copy the formula down or up rows, the row number will change – so the row number will change, but the column letter will not.  The same goes when you place a $ sign in front of the row number.  When you use a $ sign on both the column letter and number, the formula will always point to this cell.

The reason the answer could be =E4*$E$1 or =E4*E$1 is because the first option pins the formula to that cell, and the second option pins it to the row – but because in the example the formula was only being copied down, and not sideways, it will still point at the right cell.

We consider this basic knowledge of Excel because without knowing this, errors can be made with even the most basic formulas.

To make a sheet fit on one page of paper when printed out, you need to set the Width and Height to 1 page on the scale to fit settings:

The icon for autosum is this one:

When you use this tool, it will automatically insert a formula that sums up the values in cells above it.  This saves you from having to type a formula.

The only real safe way to send information to someone without them being able to easily change the contents or see the formulas you have used is to send it as a PDF:

Remember that the formulas you use could give away information you don’t want the customer to see.  Also if you have hidden columns, these could be unhid by someone else, revealing information you don’t want them to see – that is not possibly with a PDF.