Don’t worry…nothing is wrong – it can be sorted quickly.
This symbol will appear automatically when your formula links to another worksheet.
Eg If your formula reads = AberdeenSales!B8 then this is simply linking to the cell B8 in the spreadsheet called AberdeenSales.
You can do this by clicking on the cell in the first spreadsheet and typing = then simply go to and click in the cell B8 in the spreadsheet called AberdeenSales then press the Enter key. The formula reference AberdeenSales!B8 will automatically go into your formula.
There may be occasions when you want to use a value in one cell in your formula but cannot allow the formula to change the cell reference when you copy it down or across your spreadsheet. For instance in the example below the formula in C11 in the Surcharge column references cell B5 (which is the Surcharge rate) but if you copy this formula down to C12 the B5 reference will change to B6. This is not what we need in cell C12…we need it to read =B12*B5.
We want to make the cell B5 ‘Absolute’…in other words we don’t want it to be able to move from that cell reference.
We can make the ‘B’ part of the cell absolute by putting a $ in front of the ‘B’ and we can make the ‘5’ part of the cell absolute in the same way. If we therefore never want excel to change the reference of this particular cell at all the formula would read =B11*$B$5.
A quick way to put the $ in is to tap the ‘F4’ key on your keyboard after typing the B5…you will find that the $ signs will go in before the B and the 5. This is a toggle and so you can tap it again and again to see other variations if you don’t need both $ signs.
This is covered in more detail in Chapter 5 of Excel Level 1 – Formulas and Functions.
Microsoft Excel is a spectacularly useful and well-used software application that forms part of the Office suite of solutions that has been a pillar of PC use around the world now for a couple of decades.
Why use Excel?
The essential purpose of the software is to make it easier for people to organise, manage, categorise and analyse any data they may have cause to be getting to grips with. The latest versions of Excel are capable of a wide range of often quite remarkable calculations and operations but the software is still used primarily for more basic purposes and for the simple saving and sharing of information.
Opening a document
To open a new Excel document you’ll need to go to the ‘Start’ button on the left hand corner of your PC and look through the programmes you have available until you find the right icon. Alternatively, if you are working with the Windows 8 operating system then you’ll find an Excel app on your dedicated ‘Start’ page along with all the other tools or apps you’ve downloaded or installed.
Understanding your spreadsheet
Once you’ve open a new Excel document, you’ll be faced with what is referred to as being a spreadsheet and which you’ll see has columns running horizontally and vertically. You’ll see also that the system uses numbers on the left hand axis and letters across the top. The columns along both axes run on indefinitely if you scroll down or over to the right so there’s theoretically no limits to the amount of data that can be entered into any Excel document. It’s generally best though to make sure that your documents remain somewhat manageable, organised and intelligible rather than letting them turn into something more like a sprawling mess. You can add new sheets to a single document by clicking the + icon at the foot of the page to help in that regard.
Entering your data
You’ll find as you become more familiar with the software that there are lots of interesting and potential very valuable ways in which an Excel spreadsheet document can be utilised. However, when you’re just getting started you’ll want first to be aware that the columns can be adjusted for size simply by placing your cursor on a single dividing line, holding the left mouse button down and manoeuvring the cursor and your line into a new position. You’ll see also that by clicking on a particular box it becomes highlighted and that column is identified for you in a rectangular box below the banner toolbar as ‘C9’, ‘H12’, ‘N22’ etc. based on the numbered and lettered vertical and horizontal axes. To enter data or information of any kind into a particular box in your Excel spreadsheets you can click on it directly and start typing, or you can write or paste into the elongated column above the lettering of the horizontal axis. To enter this content, whatever might be, whether it’s written words or numbers or anything else, simply hit the odd-shaped ‘return’ button that you probably know by now is the largest sat there on your keyboard. Hitting return enters your data into the Excel spreadsheet in the particular column you’re working in and moves you on automatically to the next one below.
Useful tricks and tools
You can adjust your fonts and text sizes very easily in Excel in the same way as you would in Word, which means making your selection with the help of two dropdown menus that sit side-by-side in the banner toolbar. You might notice that there are options there also relating to formulas, page layouts and some quite advanced data techniques. We will leave these for now but you might find at some stage that there are tools integrated into Excel that you find massively helpful in processing your data and doing the kind of math equations that most of us would struggle with at the best of times. Even getting started though with Excel can be very worthwhile whatever area of work or study you’re involved in, so hopefully this brief crash course has been of some use. As ever though, it is practice and persistence that really helps improve your skills with particular sorts of software.
For more information about our Excel Training courses please follow the link.
There are a number of ways we can use this box but perhaps the most useful is for navigation.
If you are clicked on cell A1 for instance you will see the cell reference A1 showing in the name box which appears under the ribbon as shown in the picture below. You will also see that there is a drop down arrow but when you click on it there is generally nothing there.
Use the name box to navigate to a particular cell
Click on the name box and type the cell reference and that particular cell reference will become the active cell. ie type C4 into the name box as in the pic below and see your active cell jump from Al to C4.
Create navigation points in the drop down
If you have a particularly large workbook with many worksheets you may find it easier to find the areas you need to access regularly if you define names for those particular areas so that you can simply click on the drop down of the name box and go straight to the area. See pic below:
In order to create the name NZ-Sales in the example above you would go to the cell B13 which represents the New Zealand Sales total and then simply go to the name box and type the name you want to call this cell ie NZ_Sales.
You can also do this for a range of cells by simply selecting the range of cells first and then giving it a name in the name box.
There are many other ways you can work with defining names and labels. You can find more information in Chapter 9 of the Excel Level 2 course – Defined Names. For more information of Excel courses please click here.
The majority of functions can be found in the Formula tab by clicking on the drop down arrow of the category of function in the Function Library section…see pic below:
You can however also find a complete list in the Microsoft Help system by clicking on the question mark icon ( ? ) at the top right of your Excel screen.
This will connect you to Office.com where all the ‘Help’ support is located.
When the ‘Excel Help’ screen appears type ‘excel functions’ into the search box.
The following screen will then appear:
You can click on any of the links – the first will give you a categorised list and the one marked ‘Excel functions (alphabetical)’ will give you the full list in alphabetical order.
If you need any further help with Excel functions why not call F1plus on 01224 619780 or browse through our website at www.f1plus.co.uk to find out about our Excel courses.