Free Excel Training Online Lesson Two: Working with Numbers

2KO Africa welcomes you back to our free Excel training online. Now that we have had a look at basic formulas, let's look at how to work with numbers.

 

Lesson 2

Getting your numbers to co-operate in Excel.

Excel is the most wonderful program for manipulating and working with numbers. If your task comprises mostly text, then use MS Word rather. But if you need to put together a set of numbers and then work with those numbers to extract totals, and statistics and reports, then Excel is your program.

 

Formatting Numbers

When you enter a value / number into a cell in Excel, the number is automatically right aligned. This means it rests against the right edge of the cell, as opposed to text, which sits on the left edge (left alignment).

Look at the table below to see this:

 


Table 3

 

In the above table, you see that the word Year is a heading and is left aligned (text), while 2019 is a value.

Some numbers appear different to others. Have a loom at the following numbers to see what we mean:


100.00
2.5
25%
0.13754
R10.00

Each of the above numbers has an inherent value, but they look different. This appearance is what we call formatting. When a number is entered into a blank cell in a new workbook, Excel uses the General Format. This means that if you enter 50 for example, it will appear simply as 50, but not 50.0, nor 50%.


If a number has a decimal, and is entered with its decimal, then the decimal will appear.
Example 25.5


The above number is the same as 25 and a half


Decimals like 0.1 are sometimes more easily understood when shown as a percentage. So, in Excel it is simple to change the appearance of a number (format).

 


Table 4

 

Look at the above table, Table 4. In this table there are two sets of numbers. The first set of three numbers are all equal to 10. But, when formatted with 2 decimal places, 10 becomes 10.00. when formatted to currency (in SA Rands), a 10 becomes R10.00

 

The value underpinning the cell is 10 in each case. Formatting is simply a way to make numbers appear in a different way / display. In the second set of numbers, the value is 0.1 in both cases. The first one is entered 0.1, and left as is (general format). The second number is entered as 01. But is then formatted so that it appears as a percentage

The value underpinning the cell is 0.1 in each case. Formatting is simply a way to make numbers appear in a different way / display.

 

To format numbers which are already entered into Excel cells, follow these steps:

 

Format using the Menu

1. Select the cells containing the numbers
2. Make sure that the Home menu tab is selected
3. From the Numbers Group, click on General, and choose the desired option

 

 

4. Alternatively, click one of the button icons like the $ symbol, or the % sign, or the comma button.
5. The numbers that are currently selected will immediately change to the new format.

 

ALTERNATE METHOD:
Format Numbers using Right Click

1. Select the cells containing the numbers
2. Point the Mouse Cursor onto one of the selected cells
3. Right click
4. Choose Format Cells
5. Pick your desired format from the list

 


 

 

6. The numbers that are currently selected will immediately change to the new format.
 

 

Formatting Numbers to Consistent Number of Decimals

When numbers are entered with a different number of decimals, the list can look awkward. Have a look at Table 5 below.

 


Table 5

 

The appearance of these numbers on the eye is one that is uncomfortable. So, to convert / format these numbers to a uniform appearance will make everything look better.

Look at the result of formatting to 2 decimal places.

 


Table 6

 

 

How to Change the Number of Decimals


When numbers are formatted to Number Format or using the Comma icon (in the Number group under the Home menu tab), they automatically have 2 decimals assigned.

Here are the steps needed to follow to reduce the number of decimals from 2 to 1, or even to zero.


1. Select the number(s) to be formatted, by highlighting them.
        a. For the sake of this exercise, we assume the numbers have already been formatted to Number Format, which means they will have two decimal places.
 

2. Now click the Decrease decimals button

 

 

 

3. NB -  To increase the number of decimals, click the Increase Decimals button (the one to the right of the comma icon.

 

 

Practise on your own

Create the following on a blank Excel sheet

 

 

1. Format all numbers in column A to Number Format
2. Format all numbers in column B to 1 decimal place
3. Format all numbers in column C to Currency US $ or Rands

 


Check your Answers:
If you would like to check your answers, click here 
  (Note the file will open in Excel)
 

To Start Lesson 3, click here
To go back to the index of free online excel lessons from 2KO Africa in Cape Town, click here