Working with spreadsheets/Spreadsheet design/Formatting data

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

Alignment

To align text in a cell:

1. Click the cell you want to work with
2. Click on one or more of the following in the Alignment Group:

Text Formatting

Select the cell you want to format, then from the Font Group:

Activity

We are going to apply some simple formatting to the text in our spreadsheet. Click in the cell that contains the heading Business Income and Expenditure (Hint: even though this heading appears to spread across several cells, it’s actually located in cell A1) From the Home tab, locate the Font group. If you can see Arial then go to the next step. Otherwise click on the down arrow next to the current font name and choose Arial from the list. While you are still in the font group, change the font size to 18. Click on the heading Income – this selects the cell we wish to change. From the Font Group, choose Bold – this applies bold formatting to the selected cell. Select the heading Expenses, and apply Bold formatting. Save your work. Now we are going to format some cells to be right-aligned: Select the cell that contains Books Sold. Apply Align Text Right from the Alignment Group. Select the range underneath the heading Expenses, that contains your complete list of expenses (There should be 7 cells) Apply Align Text Right from the Alignment Group Your sheet should look something like this: Save your work and close Excel.

Let's look at entering some more data into our spreadsheet, and the effect it has on formatting:

Mr Mann has been running his bookshop for 3 months now. He’s got some figures for you to enter into the spreadsheet that you are creating for him:

Activity

Open your spreadsheet, and enter in the data. We are going to use the row under the heading Income to put in the months of the year. When you have finished you should have something that looks like the next page: We have highlighted a problem that occurs sometimes – If your column is not wide enough Excel may not display the whole of a number. It displays a series of hash marks “###” instead. If this is the case, widen the column and you should see the whole of the number appear.

As you entered the prices into Excel, you may have added the dollar sign in front.

If you did, then Excel will automatically pick up that you mean “this value is currency”. If you didn't, we will need to format the cells in our sheet.

If you have used Microsoft Word, then you know that formatting text does not change the text, it just changes the appearance of the text. Formatting in Excel does the same thing - let's look at some further formatting options.

Activity

Open your bookshop spreadsheet. Your sheet may or may not have the correct formatting, but we’re going to have a play so you can see what happens. Highlight the cell range B5:D5. From the Home tab, go to the Number group. Click on the button that has a dollar sign ($) on it. The drop-down menu that appears will have a number of different currency formats on it. Choose Euro. You will see that Excel has changed your dollar signs to Euros. Select the same three cells and apply New Zealand currency formatting by repeating the previous steps.

We actually have a full range of formatting available to us. Just above the currency button that we just used ($) is a field showing what format is in use: the default is General. Click on the drop-down arrow next to this to see other formatting options available.

Some simpler formatting options you might find useful are:

General vs Text

Both of these options “do nothing” to your data. However text allows you to have a leading zero on a number. For instance, if you wanted to store the following phone number in Excel: 021 4568 7896 the general option would display 21 4568 7896

Number

Allows you to display and format numbers. You can use the increase and decrease decimals buttons to alter the number of decimal places displayed.

Currency

Format your data as currency.

Accounting

The accounting format separates the currency symbol for clarity. Eg

Short and Long Dates

Formats your data as a date. Short format is 3/12/2011 or 3/12/11. Long format is Saturday, 3 December 2011.

Percentages

Tells Excel to treat your data as a percentage.

Activity

Open your bookshop spreadsheet. Make sure the areas highlighted in light coloured area are formatted as New Zealand currency, and the areas highlighted in dark coloured area are formatted as text. Save and close your spreadsheet.

Extra resources

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. Work through the tutorial on Formatting text Don't forget to check out the video on page 2.

|