Excel Lesson – Part 1

NB: Please know that this is not the step by step guide but just a summary notes which contain details and a brief discription.

1. Format:

It is important to be aware what you want the type of format your cell is otherwise certain function won’t work or the data may not appear as you intended. Here are some pointers to be aware of:

The data starting with ‘zero’ may not include ‘zero’ under the format of general and number. It should be under text or something more specific such as ‘Special – Phone Number’

To change for cell format: Click on ‘Format’ > ‘Format Cell’ > the select the right format > OK

Another example of number formatting issue is when the cell does not display the complete value. For instance, 249,999 may appear as 250,000. This is a big problem, especially when data is involved with ranges such as commission rate.

Change the number format by increasing the decimal points. This can be changed via:

Home tab > Number section > click on the decimal place icon (increase or decrease decimal place)

2. Sort and Filter:

(a) Filter:

Filter helps you search for a particular data within a column.

To include filter function: Under ‘Home’ tab >  ‘Sort and Filter’ > ‘Filter’

The dropdown icon will appear with the data at the top row. The filter will allow you to search for any particular data

(b) Sort:

Sort helps you sort out the data how you like such as alphabetical, such as ‘A to Z’ or ‘Z to A’.

(c) Sort default:

…

3. Manipulating Data:

Here are some functions to merge or split the data

(a) Combining data:

To do this, use the function ‘Concatenate’, which will merge the data together.

The formula is: =CONCATENATE(text1, [text2], …)

Select ‘fx’ > search and select ‘concatenate’ > following the instruction of selecting which text you want to combine.

(b) Splitting data:

Data can be split into different cells.

     i. Splitting via delimiter

Splitting data between characters such as comma, semi-colon, space etc…

Under the ‘Data’ tab > Select ‘Text to Columns’ > Select ‘Delimited’ > Select the delimiters of choice

     ii. Splitting via  field widths

Under the ‘Data’ tab > Select ‘Text to Columns’ > Select ‘Fixed width’ > Click on where you want the

(c) Extracting data:

To extract a certain part of the data into a cell, use these following formula:

     i. =LEFT(Text, No of Character)

The LEFT function extract information from the left-hand side moving towards the right direction

     ii. =MID(Text, Start No, No of Character)

The MIDDLE function extract information from anywhere you want within the data.

     iii. =RIGHT(Text, No of Character)

The RIGHT function extract information starting from the right end of data moving forwards the left direction.

These can be useful if you want to just extract data such as postcode from the left of the telephone number without the code from the right.

You can also drag the formula down so that it extracts data from the whole column.

(d) Conditional formatting: 

Conditional formatting helps sets the rules for the cell. For example, if dates are overdue or going to be overdue, you can flag it out by conditional formatting so that the font can appear a different colour or have icons appear next to the date with warning icons.

Click on ‘Conditional formatting’ > Select the types of formula.

If you want to edit the formula, click on the cell you wish to edit, select ‘conditional format’ and then ‘manage rules’.

The ‘If’ functions are usually used in excel to formulate rules for the conditional format. This will be explained later.

Some examples:

1. Changing font colour depending on date range

 

2. Changing font colour depending on date range and status

=IF(N3=”Complete”,0,R3<TODAY())

3. Include flag icon

(e) Replacing error signs:

=IFERROR(value, value if error)

Example:

To make it blank

=IFERROR(VLOOKUP(…), “”)

(f) Replacing empty cells:

 

4. Mathematical Calculation:

Here are common mathematical formulas, especially used in financial accounting

• Basic: Addition (+), subtraction (-), division (/) and multiple (*)
• Sum: =SUM(…)
• Invoice/receipt formulas:
  •  Net amount: Usually given
  • Tax amount: (Net amount) * (Percent/100)
  • Gross amount: (Net amount) + (Tax amount)
  • Find Net amount: (Gross amount) / ((100+Tax%)/100
  • Find Tax amount: (Gross) – (Net)
  • Find Tax percentage: ((Net-Gross)/Net)*100
• Compound
  • Function: =FV(Interest Rate, Period, (-) Present Value
  • Function:
  • Annual Interest Rate: (Locked Initial amount) * (1 + Locked Interest rate%) ^ Years
  • Quarterly Interest Rate: =P*(1+r/4)^(n*4)
  • Monthly Interest Rate: =P*(1+r/12)^(n*12)