Working with spreadsheets/Spreadsheet calculations/Formulas

Working with Spreadsheets

Basic skills applicable to planning, creating and formatting an Excel spreadsheet.
An OP Online course from the National Certificate in Computing (Level 2) programme.

About this course  |  Getting started  |  Course schedule  |  Assessment overview  |  Help

Formula View

Occasionally its handy to be able see all of your formulas (insead of the results) in your spreadsheet. Click on:

Formulas > Formula Auditing > Show Formulas

Or use the keyboard shortcut Ctrl ~

If you print from this view you will print your formulas out. Press Ctrl~ again to revert the sheet back to normal.

Formatting a spreadsheet

Before we go on to add more data and formulas to the spreadsheet, lets look at how to use some more features for formatting in Excel :

Activity

Please note: the following link will open in a new window/tab. When you have finished, simply close the window/tab and you'll return to this page. Watch the video on Customize Your Spreadsheet

Activity

Open your bookshop spreadsheet. Use Excel's formatting features to customise your spreadsheet. Here, we used colour to distinguish the expenses from the income: Save your spreadsheet.

Extending the spreadsheet

We often need to change a spreadsheet to meet future needs. In the next activity we will add more data and formulas.

Activity

Mr Mann has provided us with some more information for his spreadsheet. He knows that this year there will be some increases in his expenses. He would also like to start drawing some income from his shop. Mr Mann has given us some figures based on previous years sales, for projected income for the rest of the year: You will need to: Enter the information into the spreadsheet. Copy across the formulas that you need for the monthly subtotals and monthly cash. Copy the formatting over. Save your spreadsheet. Mr Mann would like a grand total for the year. If he adds up all his monthly totals he can see if he is in the black, or in the red! Click in cell N20. Use the auto-sum button to help you insert a SUM function for this. Mr Mann can see that he is going to end the year at a loss. As we are already in April for the current business year, how much extra income per month is Mr Mann going to have to generate to come out in the black by the end of December? (Hint, use the extra line in your income section.)

|