EXCEL lessons with examples


Télécharger EXCEL lessons with examples

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Introduction to Microsoft Excel 101 

What is Microsoft Excel?

Microsoft Excel is a spreadsheet program that is used to record and analyse numerical data. Think of a spreadsheet as a collection of columns and rows that form a table. Alphabetical letters are usually assigned to columns and numbers are usually assigned to rows. The point where a column and a row meet is called a cell. The address of a cell is given by the letter representing the column and the number representing a row. Let's illustrate this using the following image. 

Why Should I Learn Microsoft Excel? 

We all deal with numbers in one way or the other. We all have daily expenses which we pay for from the monthly income that we earn. For one to spend wisely, they will need to know their income vs. expenditure. Microsoft Excel comes in handy when we want to record, analyze and store such numeric data. 

Where can I get Microsoft Excel? 

There are number of ways in which you can get Microsoft Excel. You can buy it from a hardware computer shop that also sells software. Microsoft Excel is part of the Microsoft Office suite of programs. Alternatively, you can download it from the Microsoft website but you will have to buy the license key. 

In this tutorial, we are going to cover the following topics. 

•    How to Open Microsoft Excel?

•    Understanding the Ribbon

•    Understanding the worksheet

•    Customization Microsoft Excel Environment?        Important Excel shortcuts

How to Open Microsoft Excel?

Running Excel is not different from running any other Windows program. If you are running Windows with a GUI like (Windows XP, Vista, and 7) follow the following steps. 

•    Click on start menu

•    Point to all programs

•    Point to Microsoft Excel

•    Click on Microsoft Excel

Alternatively, you can also open it from the start menu if it has been added there. You can also open it from the desktop shortcut if you have created one. 

For this tutorial, we will be working with Windows 8.1 and Microsoft Excel 2013. Follow the following steps to run Excel on Windows

8.1 

•    Click on start menu

•    Search for Excel N.B. even before you even typing, all programs starting with what you have typed will be listed.

•    Click on Microsoft Excel

The following image shows you how to do this 

Understanding the Ribbon

The ribbon provides shortcuts to commands in Excel. A command is an action that the user performs. An example of a command is creating a new document, printing a documenting, etc. The image below shows the ribbon used in Excel 2013. 

Ribbon components explained

Ribbon start button - it is used to access commands i.e. creating new documents, saving existing work, printing, accessing the options for customizing Excel, etc. 

Ribbon tabs – the tabs are used to group similar commands together. The home tab is used for basic commands such as formatting the data to make it more presentable, sorting and finding specific data within the spreadsheet. 

Ribbon bar – the bars are used to group similar commands together. As an example, the Alignment ribbon bar is used to group all the commands that are used to align data together. 

Understanding the worksheet (Rows and Columns, Sheets, Workbooks)

A worksheet is a collection of rows and columns. When a row and a column meet, they form a cell. Cells are used to record data. Each cell is uniquely identified using a cell address. Columns are usually labelled with letters while rows are usually numbers. 

A workbook is a collection of worksheets. By default, a workbook has three cells in Excel. You can delete or add more sheets to suit your requirements. By default, the sheets are named Sheet1, Sheet2 and so on and so forth. You can rename the sheet names to more meaningful names i.e. Daily Expenses, Monthly Budget, etc. 

Customization Microsoft Excel Environment

Personally I like the black colour, so my excel theme looks blackish. Your favourite colour could be blue, and you too can make your theme colour look blue-like. If you are not a programmer, you may not want to include ribbon tabs i.e. developer. All this is made possible via customizations. In this sub-section, we are going to look at; 

•    Customization the ribbon

•    Setting the colour theme

•    Settings for formulas

•    Proofing settings

•    Save settings

Customization of ribbon

The above image shows the default ribbon in Excel 2013. Let's start with customization the ribbon, suppose you do not wish to see some of the tabs on the ribbon, or you would like to add some tabs that are missing such as the developer tab. You can use the options window to achieve this. 

•    Click on the ribbon start button

•    Select options from the drop down menu. You should be able to see an Excel Options dialog window

•    Select the customize ribbon option from the left-hand side panel as shown below

•    On your right-hand side, remove the check marks from the tabs that you do not wish to see on the ribbon. For this example, we have removed Page Layout, Review, and View tab. ?         Click on the "OK" button when you are done.

Your ribbon will look as follows 

Adding custom tabs to the ribbon

You can also add your own tab, give it a custom name and assign commands to it. Let's add a tab to the ribbon with the text Guru99 

1.   Right click on the ribbon and select Customize the Ribbon. The dialogue window shown above will appear

2.   Click on new tab button as illustrated in the animated image below

3.   Select the newly created tab

4.   Click on Rename button

5.   Give it a name of Guru99

6.   Select the New Group (Custom) under Guru99 tab as shown in the image below

7.   Click on Rename button and give it a name of My Commands

8.   Let's now add commands to my ribbon bar

9.   The commands are listed on the middle panel

10. Select All chart types command and click on Add button

11. Click on OK

Your ribbon will look as follows 

Setting the colour theme

To set the color-theme for your Excel sheet you have to go to Excel ribbon, and click on à File àOption command. It will open a window where you have to follow the following steps. 

1.   The general tab on the left-hand panel will be selected by default.

2.   Look for colour scheme under General options for working with Excel

3.   Click on the colour scheme drop-down list and select the desired colour

4.   Click on OK button

Settings for formulas

This option allows you to define how Excel behaves when you are working with formulas. You can use it to set options i.e.

autocomplete when entering formulas, change the cell referencing style and use numbers for both columns and rows and other options.

If you want to activate an option, click on its check box. If you want to deactivate an option, remove the mark from the checkbox. You can this option from the Options dialogue window under formulas tab from the left-hand side panel 

Proofing settings

This option manipulates the entered text entered into excel. It allows setting options such as the dictionary language that should be used when checking for wrong spellings, suggestions from the dictionary, etc. You can this option from the options dialogue window under the proofing tab from the left-hand side panel 

Save settings

This option allows you to define the default file format when saving files, enable auto recovery in case your computer goes off before you could save your work, etc. You can use this option from the Options dialogue window under save tab from the left-hand side panel 

Important Excel shortcuts

Ctrl + P                                                  used to open the print dialogue window 

Ctrl + N 

creates a new workbook 

Ctrl + S 

saves the current workbook 

Ctrl + C 

copy contents of current select 

Ctrl + V 

paste data from the clipboard 

SHIFT + F3 

displays the function insert dialog window 

SHIFT + F11                                           Creates a new worksheet 

F2                                                             Check formula and cell range covered 

Best Practices when working with Microsoft Excel

1.   Save workbooks with backward compatibility in mind. If you are not using the latest features in higher versions of Excel, you should save your files in 2003 *.xls format for backwards compatibility

2.   Use description names for columns and worksheets in a workbook

3.   Avoid working with complex formulas with many variables. Try to break them down into small managed results that you can use to build on

4.   Use built-in functions whenever you can instead of writing your own formulas

Summary

•    Microsoft Excel is a powerful spreadsheet program used to record, manipulate, store numeric data and it can be customized to match your preferences

•    The ribbon is used to access various commands in Excel

•    The options dialogue window allows you to customize a number of items i.e. the ribbon, formulas, proofing, save, etc.

How to Add, Subtract, Multiply, Divide in Excel

In this tutorial, we are going to perform basic arithmetic operations i.e. addition, subtraction, division and multiplication. The following table shows the data that we will work with and the results that we should expect. 

S/N 

ARITHMETIC OPERATOR 

FIRST NUMBER 

SECOND NUMBER 

RESULT 

Addition (+) 

13 

16 

Subtraction (-) 

21 

12 

Division (/) 

33 

12 

2.75 

4               Multiplication                                                         7                                                3                                                        21

Let's now use Microsoft excel to achieve the above results 

Create a folder on your computer in my documents folder and name it Guru99 Excel Tutorials 

For this tutorial, we will be using Microsoft Excel 2013. The good news is even if you have Microsoft Excel 2007 or 2010, you will still be able to follow the tutorial and get the same result. 

Open Excel. You will get a window similar to the one shown below. The outlook of Excel will depend on your version. 

•    Enter the data in your worksheet as shown in the image above.

•    We will now perform the calculations using the respective arithmetic operators. When performing calculations in Excel, you should always start with the equal (=) sign.

•    Let's start with the one for addition. Write the following formula in E2 Excel (Result column)

•    =C2+D2

HERE,

•    "=" tells Excel to evaluate whatever follows after the equal sign

•    "C2" is the cell address of the first number given by C representing the column letter and 2 representing the row number ?            "D2" is the cell address of the second number given by D representing the column letter and 2 representing the row number Press enter key on the keyboard when done. You should get 16 as the result. 

Tutorial exercise 1

Using the knowledge gained in the above example, try to write the formulas for subtraction, division, and multiplication. 

Formatting data in Microsoft Excel

We all love beautiful things don't we? Formatting in Excel helps us achieve exactly that. We can make our spreadsheets more presentable. We will use the data in the arithmetic operations table. We will make the column names; 

•    Bold

•    Align serial numbers to the left ?       Enclose the data in boxes.

Make column names bold

•    Highlight the cells that have the column names by dragging them.

•    Click on the bold button represented by B command.

•    Your workbook should now appear as follows

Align data to the left

•    We will align the serial numbers to the left

•    Highlight all the data in the S/N column

•    Click on align left as shown below

Enclose data in boxes

Highlight all the columns and rows with data 

On the font ribbon bar, click on borders command as shown below. 

You will get the following drop down menu 

Select the option "All Borders". 

Your data should now look as follows 

Tutorial Exercise 2

Using the knowledge gained above, try to change the font colour and try out other options available on the Home tab. 

Setting the print area and printing (Print View) & Page Layout

The print area is the part of the worksheet that you would like to print out on paper. The quick and easy way of doing it is by using the following shortcut commands 

Ctrl + P 

You will get the following print preview. 

Press Esc button to exit print preview mode 

Tutorial exercise 3

The page setup ribbon bar has a number of options i.e. orientation, size, etc. Try to apply the different settings and use Ctrl + P shortcut to preview the effects on the worksheet. 

Summary

In this article, we have learnt how to perform basic arithmetic operations using Excel, Formatting data, How to Setting the print area and printing (Print View). 

Excel Data Validation, Filters, Grouping 

In this tutorial, we are going to cover the following topics. 

•    Data validation

•    Data filters

•    Group and Ungroup

•    Adding images to spreadsheets

Data validation

Data validation is very important in the sense that it helps us avoid mistakes that can be avoided. Let's assume you are recording student exam marks and you know the minimum is 0 and the maximum is 100. You can take advantage of validation features to ensure that only values between 0 and 100 are entered. 

Add a new sheet in your workbook by clicking on the plus button at the bottom of the worksheet. 

Add a column for S/N, Name and Score. Your sheet should look as follows 

S/N 

Name 

Score 

Jane 

James 

Jones 

4                                                         Jonathan                                                                                       

5                                                         John                                                                                              

•    Click on the DATA tab

•    Select the cells C2 to C6 (The cells that will be used to record the scores)

•    Click on Data validation drop down list. ?     Click on Data validation.

•    You will get the following dialogue window

•    Click on Error Alert tab

•    Enter the alert title and message as shown in the diagram below.

•    Click on OK button

•    Try to enter a score greater than 200. You will get the following error message

Data filters

Data filters allow us to get data that matches our desired criteria. Let's say we want to show the results of all the students whose names start with "ja" or get scores that are less than, greater than or equal to a certain value, we can use filters to get such data.  Select the name and scores columns as shown below 

•    Click on DATA tab on the ribbon

•    Click on Sort & Filter drop down list as shown in the image below

•    Click on the Name Filter

•    Select text filters

•    Select begins with

•    You will get the following window.

•    Enter "ja" and click on "OK" button

•    You should be able to see only the results for Jane and James.

Group and Ungroup

Groups allow us to view easily and hide unnecessary details from either columns or rows. In addition to that, we can also use groups to analyse data that belongs to a common category. Let's illustrate this with an example. We will use the student scores example above. 

•    Right click on the score and select insert column. Name the name column gender.

•    Change James to Juanita. Put female for Janet and Juanita. Put male for the rest of the students. You sheet should look as follows.

We will now group the females together and display their average score and do the same for the males.

•    Click on DATA tab on the ribbon

•    Select all the columns and rows with data

•    Click on Group drop down button as shown in the image below

You will get the following window 

•    Make sure Rows options is selected

•    Click on OK button

•    You will get the following preview

•    We will now calculate the average scores for females and males

•    Select the whole data as shown below

Click on Subtotal drop down button under DATA tab 

You will get the following window 

•    Set "At each change" into gender

•    Set "Use function" to average

•    Select "Add subtotal" to Score

•    Click on "OK" button

Adding images to spreadsheets

At times, you would like to brand the documents printed in excel with letterhead information and print with the company logo, etc. Excel has features that allow you to import images into Excel. The command for adding images is found under the INSERT tab on the ribbon. 

You will get the following dialogue window 

•    You can browse to any folder on your computer that has pictures, and you can select any picture of your choice.

•    You will get results similar to the ones shown below

Tutorial exercise 4

Use the INSERT tab pictures command to add a picture of your choice to the worksheet. 

Summary

In this article, we have learnt how to perform basic arithmetic operations using Excel, format the data, and apply validation rules, filter data and how to take advantage of groups to further analyse data and improve presentation. 

Best Excel Formulas & Functions: You Need to Know Now 

Details

Last Updated: 27 July 2018 

Formulas and functions are the building blocks of working with numeric data in Excel. This article introduces you to formulas and functions. 

In this article, we will cover the following topics. 

•    What is a formula?



•    Mistakes to avoid when working with formulas in Excel

•    What is a function? (Function Wizard)

•    The importance of functions

•    Common functions

•    Numeric functions

•    String functions

•    Date Time functions

•    V Lookup function

Tutorials Data

For this tutorial, we will work with the following datasets. 

Home supplies budget 

S/N 

ITEM 

QTY 

PRICE 

SUBTOTAL 

Is it Affordable? 

Mangoes 

600 

Oranges 

1200 

Tomatoes 

2500 

Cooking Oil 

6500 

Tonic Water 

13 

3900 

House Building Project Schedule 

S/N 

ITEM 

START DATE 

 

END DATE 

DURATION (DAYS) 

Survey land 

04/02/2015 

 

07/02/2015 

Lay Foundation 

10/02/2015 

 

15/02/2015 

Roofing 

27/02/2015 

 

03/03/2015 

Painting 

09/03/2015 

 

21/03/2015 

What is a formula?

It is the simplest form, a formula is an expression made up of cell addresses and arithmetic operators. Formulas can also be made up of discrete values i.e. =6*3. Excel evaluates the formula to a value. An example of a formula looks as follows.  =A2 * D2 / 2 

HERE, 

•    "=" tells Excel that this is a formula, and it should evaluate it.

•    "A2" * D2" makes reference to cell addresses A2 and D2 then multiplies the values found in these cell addresses. ?           "/" is the division arithmetic operator

•    "2" is a discrete value

Formulas practical exercise

We will work with the sample data for the home budget to calculate the subtotal. 

•    Create a new workbook in Excel

•    Enter the data shown in the home supplies budget above.

•    Your worksheet should look as follows.

We will now write the formula that calculates the subtotal 

Set the focus to cell E4 

Enter the following formula. 

=C4*D4  HERE, 

•    "C4*D4" uses the arithmetic operator multiplication (*) to multiply the value of the cell address C4 and D4.

Press enter key

You will get the following result 

The following animated image shows you how to auto select cell address and apply the same formula to other rows.

Mistakes to avoid when working with formulas in Excel

1.    Remember the rules of Brackets of Division, Multiplication, Addition, & Subtraction (BODMAS). This means expressions are brackets are evaluated first. For arithmetic operators, the division is evaluated first followed by multiplication then addition and subtraction is the last one to be evaluated. Using this rule, we can rewrite the above formula as =(A2 * D2) / 2. This will ensure that A2 and D2 are first evaluated then divided by two.

2.    Formulas usually work with numeric data; you can take advantage of data validation to specify the type of data that should be accepted by a cell i.e. numbers only.

3.    To ensure that you are working with the correct cell addresses referenced in the formulas, you can press F2 on the keyboard. This will highlight the cell addresses used in the formula, and you can cross check to ensure they are the desired cell addresses.

4.    When you are working with many rows, you can use serial numbers for all the rows and have a record count at the bottom of the sheet. You should compare the serial number count with the record total to ensure that your formulas included all the rows.

Check Out Top 10 Excel Formulas

What is a function? (Function Wizard)

A function is a predefined formula. Functions allow you to use descriptive names to automatically apply formulas for you. Examples of functions include; 

•    SUM for summation of a range of numbers

•    AVERAGE for calculating the average of a given range of numbers

•    COUNT for counting the number of items in a given range

The importance of functions

Functions increase user productivity when working with excel. Let's say you would like to get the grand total for the above home supplies budget. To make it simpler, you can use a formula to get the grand total. Using a formula, you would have to reference the cells E4 through to E8 one by one. You would have to use the following formula. 

= E4 + E5 + E6 + E7 + E8 

With a function, you would write the above formula as 

=SUM (E4:E8) 

As you can see from the above function used to get the sum of a range of cells, it is much more efficient to use a function to get the sum than using the formula which will have to reference a lot of cells. 

Common functions

Let's look at some of the most commonly used functions in Excel. We will start with statistical functions. 

S/N FUNCTION  CATEGORY DESCRIPTION                                                                                        USAGE

01  SUM        Math & Trig  Adds all the values in a range of cells  =SUM(E4:E8) 

02  MIN          Statistical        Finds the minimum value in a range of cells              =MIN(E4:E8) 

03  MAX        Statistical        Finds the maximum value in a range of cells             =MAX(E4:E8) 

04  AVERAGE  Statistical     Calculates the average value in a range of cells       =AVERAGE(E4:E8) 

05  COUNT  Statistical        Counts the number of cells in a range of cells           =COUNTS(E4:E8) 

06  LEN         Text     Returns the number of characters in a string text      =LEN(B7) 

07  SUMIF     Math & Trig  Adds all the values in a range of cells that meet a specified criteria      =SUMIF(D4:D8,">=1000",C4:C8)

08  AVERAGEIF Statistical  Calculates the average value in a range of cells that meet the specified criteria =AVERAGEIF(F4:F8,"Yes",E4:E8) 

09  DAYS      Date & Time  Returns the number of days between two dates         =DAYS(D4,C4) 

10  NOW       Date & Time  Returns the current system date and time       =NOW() 

Numeric functions

As the name suggests, these functions operate on numeric data. The following table shows some of the common numeric functions. 

S/N  FUNCTION 

CATEGORY 

DESCRIPTION 

USAGE 

ISNUMBER 

Information 

Returns true if the supplied value is numeric and false if it is not numeric 

=ISNUMBER(A3) 

RAND 

Math & Trig 

Generates a random number between 0 and 1 

=RAND() 

ROUND 

Math & Trig 

Rounds off a decimal value to the specified number of decimal points 

=ROUND(3.14455,2) 

MEDIAN 

Statistical 

Returns the number in the middle of the set of given numbers 

=MEDIAN(3,4,5,2,5) 

PI 

Math & Trig 

Returns the value if Pi 

=PI() 

POWER 

Math & Trig 

Returns the result of a number raised to a power 

=POWER(2,4) 

MOD 

Math & Trig 

Returns the remainder when you divide two numbers 

=MOD(10,3) 

ROMAN 

Math & Trig 

Converts a number to roman numerals 

=ROMAN(1984) 

String functions

These functions are used to manipulate text data. The following table shows some of the common string functions. 

S/N FUNCTION CATEGORY DESCRIPTION 

USAGE 

1  LEFT               Text 

Returns a number of specified characters from the start (left-hand side) of a string 

=LEFT("GURU99",4) 

2  RIGHT             Text 

Returns a number of specified characters from the end (right-hand side) of a string 

=RIGHT("GURU99",2) 

3  MID                 Text 

Retrieves a number of characters from the middle of a string from a specified start position and length 

=MID("GURU99",2,3) 

4  ISTEXT            Information  Returns true if the supplied parameter is 

=ISTEXT(value) 

FIND            Text 

Returns the starting position of a text string within another text string. This function is case-sensitive. 

=FIND("oo","Roofing",1)

REPLACE  Text 

Replaces part of a string with another specified string 

=REPLACE("Roofing",2,2,"xx")

Date Time Functions

These functions are used to manipulate date values. The following table shows some of the common date functions 

S/N  FUNCTION 

CATEGORY 

DESCRIPTION 

USAGE 

1        DATE 

Date & Time 

Returns the number that represents the date in excel code 

=DATE(2015,2,4) 

2        DAYS 

Date & Time 

Find the number of days between two dates 

=DAYS(D6,C6) 

3        MONTH 

Date & Time 

Returns the month from a date value 

=MONTH("4/2/2015") 

4        MINUTE 

Date & Time 

Returns the minutes from a time value 

=MINUTE("12:31") 

5        YEAR 

Date & Time 

Returns the year from a date value 

=YEAR("04/02/2015") 

VLOOKUP function

The VLOOKUP function is used to perform a vertical look up in the left most column and return a value in the same row from a column that you specify. Let's explain this in a layman's language. The home supplies budget has a serial number column that uniquely identifies each item in the budget. Suppose you have the item serial number, and you would like to know the item description, you can use the VLOOKUP function. Here is how the VLOOKUP function would work. 

=VLOOKUP (C12, A4:B8, 2, FALSE) 

HERE, 

•    "=VLOOKUP" calls the vertical lookup function

•    "C12" specifies the value to be looked up in the left most column

•    "A4:B8" specifies the table array with the data

•    "2" specifies the column number with the row value to be returned by the VLOOKUP function

•    "FALSE," tells the VLOOKUP function that we are looking for an exact match of the supplied look up value

The animated image below shows this in action 

Download the above Excel Code

Summary

Excel allows you to manipulate the data using formulas and/or functions. Functions are generally more productive compared to writing formulas. Functions are also more accurate compared to formulas because the margin of making mistakes is very minimum. 

IF, AND, OR, Nested IF & NOT Logical Functions in Excel 

Things will not always be the way we want them to be. The unexpected can happen. For example, let's say you have to divide numbers. Trying to divide any number by zero (0) gives an error. Logical functions come in handy such cases. In this tutorial, we are going to cover the following topics. 

In this tutorial, we are going to cover the following topics. 

•    What is a Logical Function?

•    IF function example

•    Excel Logic functions explained? Nested IF functions

What is a Logical Function?

It is a feature that allows us to introduce decision-making when executing formulas and functions. Functions are used to; 

•    Check if a condition is true or false

•    Combine multiple conditions together

What is a condition and why does it matter?  

A condition is an expression that either evaluates to true or false. The expression could be a function that determines if the value entered in a cell is of numeric or text data type, if a value is greater than, equal to or less than a specified value, etc. 

IF Function example

We will work with the home supplies budget from this tutorial. We will use the IF function to determine if an item is expensive or not. We will assume that items with a value greater than 6,000 are expensive. Those that are less than 6,000 are less expensive. The following image shows us the dataset that we will work with. 

•    Put the cursor focus in cell F4

•    Enter the following formula that uses the IF function

=IF(E4<6000,"Yes","No") 

HERE,

•    "=IF(…)" calls the IF functions

•    "E4<6000" is the condition that the IF function evaluates. It checks the value of cell address E4 (subtotal) is less than 6,000

•    "Yes" this is the value that the function will display if the value of E4 is less than 6,000

•    "No" this is the value that the function will display if the value of E4 is greater than 6,000

When you are done press the enter key 

You will get the following results

Excel Logic functions explained

The following table shows all of the logical functions in Excel 

S/N  FUNCTION  CATEGORY DESCRIPTION 

USAGE 

01        AND                 Logical               Checks multiple conditions and             =AND(1 > 0,ISNUMBER(1)) 

returns true if they all the conditions evaluate to true. 

02 

FALSE 

Logical 

 

Returns the logical value FALSE. It is used to compare the results of a condition or function that either returns true or false 

FALSE 

03                                                           IF    Logical         Verifies whether a condition is met =IF(ISNUMBER("22"),"Yes", or not. If the condition is met, it      "No") 

 

returns true. If the condition is not met, it returns false. 

04 

IFERROR 

Logical 

Returns the expression value if no error occurs. If an error occurs, it returns the error value 

=IFERROR(5/0,"Divide by zero error") 

05        IFNA                Logical               Returns value if #N/A error does not =IFNA(D6*E6,0) N.B the above

occur. If #N/A error occurs, it returns        formula returns zero if both or NA value. #N/A error means a value        either D6 or E6 is/are empty  if not available to a formula or function. 

06 

NOT 

Logical 

Returns true if the condition is false and returns false if condition is true 

=NOT(ISTEXT(0)) N.B. the above function returns true. This is because ISTEXT(0) returns false and NOT function converts false to TRUE 

07        OR                    Logical               Used when evaluating multiple          =OR(D8="admin",E8="cashier")

conditions. Returns true if any or all        N.B. the above function returns of the conditions are true. Returns       true if either or both D8 and E8 false if all of the conditions are false         admin or cashier 

08 

TRUE 

Logical 

Returns the logical value TRUE. It is used to compare the results of a condition or function that either returns true or false 



TRUE 

Nested IF functions

A nested IF function is an IF function within another IF function. Nested if statements come in handy when we have to work with more than two conditions. Let's say we want to develop a simple program that checks the day of the week. If the day is Saturday we want to display "party well", if it's Sunday we want to display "time to rest", and if it's any day from Monday to Friday we want to display, remember to complete your to do list. 

A nested if function can help us to implement the above example. The following flowchart shows how the nested IF function will be implemented. 

The formula for the above flowchart is as follows 

=IF(B1="Sunday","time to rest",IF(B1="Saturday","party well","to do list"))  HERE, 

•    "=IF(….)" is the main if function

•    "=IF(…,IF(….))" the second IF function is the nested one. It provides further evaluation if the main IF function returned false.

?Practical example

Create a new workbook and enter the data as shown below 

•    Enter the following formula

=IF(B1="Sunday","time to rest",IF(B1="Saturday","party well","to do list")) 

•    Enter Saturday in cell address B1

•    You will get the following results

Summary

Logical functions are used to introduce decision-making when evaluating formulas and functions in Excel. 

S/N 

CHART

TYPE 

WHEN SHOULD I USE IT? 

EXAMPLE

       

Excel Creating Charts and Conditional Formatting 

A picture is worth of thousand words; a chart is worth of thousand sets of data. In this tutorial, we are going to learn how we can use charts in Excel to visualize our data. 

What is a chart?

A chart is a visual representative of data in both columns and rows. Charts are usually used to analyse trends and patterns in data sets. Let's say you have been recording the sales figures in Excel for the past three years. Using charts, you can easily tell which year had the most sales and which year had the least. You can also use charts to compare set targets against actual achievements. 

We will use the following data for this tutorial. 

Note: we will be using Excel 2013. If you have a lower version, then some of the more advanced features may not be available to you. 

Item 

2012 

2013 

2014 

2015 

Desktop Computers                                                               20                 12             13            12 

Laptops 

34 

45 

40 

39 

Monitors 

12 

10 

17 

15 

Printers                                                                                  78                13              90            14 

Types of charts

Different scenarios require different types of charts. Towards this end, Excel provides a number of chart types that you can work with. The type of chart that you choose depends on the type of data that you want to visualize. To help simplify things for the users, Excel 2013 and above has an option that analyses your data and makes a recommendation of the chart type that you should use. 

The following table shows some of the most commonly used charts and when you should consider using them. 

The importance of charts

•    Allows you to visualize data graphically

•    It's easier to analyse trends and patterns in the charts ?    Easy to interpret compared to data in cells

Step by step example of creating charts in Excel

In this tutorial, we are going to create a simple column chart that will display the sold quantities against the sales year. 

•    Open Excel

•    Enter the data from the sample data table above

•    Your workbook should now look as follows

To get the desired chart you have to follow the following steps 

•    Select the data you want to represent in graph

•    Click on INSERT tab from the ribbon

•    Click on the Column chart drop down button ?        Select the chart type you want

You should be able to see the following chart 

Tutorial Exercise

When you select the chart, the ribbon activates the following tab 

Try to apply the different chart styles, and other options presented in your chart. 

Conditional Formatting

As the name suggests, conditional formatting refers to formatting that is only applied when the specified condition is true. Let's say we want to easily fetch the sales figures greater than 30. We can use conditional formatting to highlight all the sales figures that are greater than 30. 

Highlight all the sales figures as shown below 

Click on Conditional Formatting drop-down menu on the HOME tab 

You will get the following dialogue window when you click on Greater Than… 

The final result should look as follows 

If you want to remove the conditional formatting, 

•    Click on conditional formatting button

•    Select clear rules

•    Select clear rules from the entire sheet

Summary

Charts are a powerful way of graphically visualizing your data. Excel has many types of charts that you can use depending on your needs. 

Conditional formatting is also another power formatting feature of Excel that helps us easily see the data that meets a specified condition 

How to Create Budget In Excel Spreadsheet with Template 

Details

Last Updated: 27 July 2018 

"Money is a tool. Used properly it makes something beautiful- used wrong, it makes a mess!" - Bradley Vinson 

It takes discipline to use money properly. In this tutorial, we are going to look at how we can use Excel to properly manage our personal finances. We will cover the following topics. 

•    Why manage Budget?

•    Major components of a personal finance system

•    Using Excel to set personal budgets, record income and expenses

•    Visualizing the data using charts

Why manage Budget?

Let's face it, the world we live in is fuelled by money. We go to school to get a good job, engage in business and other related activities with the main goal of making money. If we do not manage our personal finances properly, then all of our efforts go to waste. 

Most people spend more than they earn. In order to be financially successful, one needs to develop a habit of spending less than they earn and invest the surplus in business ventures that will multiply the invested money 

Major components of a personal finance system

This is a basic personal finance system so we will consider the following components; 

1.    Projected income – this is the money that you expect to earn now and in the future.

2.    Budget – this is a list of the items that you expect to buy, quantities and their respective prices

3.    Actual income – this is the actual money that you earn as time progresses

4.    Actual expenditure – this is the money that you actually spend buying things

The variance between the projected income and actual income gives us the performance indicator of how accurate our estimates are or how hard we are working. 

The variance between the budget and the actual expenditure give us the performance indicator of how disciplined we are when it comes to sticking to a budget. 

Since saving is a part of the goal of having a personal finance system, the actual income vs. the actual expenditure say on a monthly basis gives us an idea of how much we would save over a year. 

Using Excel to set personal budgets, and record income and expenses

We have looked at the components of a personal finance system, and we will now use what we have learnt so far to implement the above. We will create two workbooks for this tutorial, one for income and the other for budgets. 

When you are done with this tutorial, your workbooks should look as follows 

Open Excel and create a new workbook 

Income sheet

•    Rename the Sheet1 to Income

•    Enter the data shown below

•    Projected Monthly Income

S/N 

Description 

Amount ($) 

Salary 

600.00 

Freelance works 

250.00 

Grand Total 

Expected annual income:  

Others

180.00

Actual Income 

S/N 

Description 

Amount ($) 

Salary 

600.00 

Freelance works 

200.00 

Grand Total 

Actual annual income: 

Others

150.00

Formulas for Income sheet We will now need to; 

1.    Calculate the monthly income for both projected and actual income.

2.    Compute the projected annual income and actual annual income based on the monthly total.

3.    Find the variance between the actual and project annual income

4.    Show tips on how we are doing using logical functions

5.    Use conditional formatting to highlight how well we are managing our finances

Tutorial Exercise 1

1.    Find the sum of all monthly income sources. Use the SUM function. Do this for both projected and actual monthly income.

2.    Find the annual projected and actual income by multiplying your answer in question 1 by 12.

3.    Find the income variance by subtracting the projected annual income from the actual annual income.

4.    Add a comments row just below the tables. Use the IF functions to display "You need to work extra hard to meet your income targets" if the variance is less than zero (0) else display "Great job working smart and harder".

5.    Use conditional formatting to change the text colour to red if the variance is less than 0 else change the text colour to green.

If you are stuck on what to do, read the articles on formulas and functions, and Visualizing data using charts in Excel.

Expenditure sheet

Add a new sheet and rename it to Expenditure 

Enter the data as shown below 

Budget 

S/N 

   

Item 

Qty 

Price 

Subtotal 

   

Rentals 

12 

210.00 

2  

   

Bills 

12 

100.00 

3  

   

Groceries 

12 

230.00 

4  

   

School 

500.00 

5  

   

Miscellaneous 

133.00 

Grand Total:                                                                                                                                                                                                                                                                                                                                                                          

Expected Annual savings:                                                                                                                                                                                                                                                                                                                                                  

Actual income 

S/N 

Description 

Qty 

Price 

Subtotal 

Rentals 

12 

210.00 

Bills 

12 

145.00 

Groceries 

12 

240.00 

School 

500.00 

Miscellaneous 

233.00 

Actual Annual Savings: 

Grand Total: 

Formulas for Expenditure sheet

We will need to; 

1.    Calculate the subtotal

2.    Calculate the grand total based on sub totals

3.    Compute the expected annual savings. The expected annual savings is the difference between the projected annual income and the budget total amount. This will be done for the actual income and actual expenditure too.

4.    Compute the monthly savings variance

5.    Add a comments row just below the tables. Use the IF functions to display "You need to minimize your expenses." if the variance is less than zero (0) else display" Great job sticking to the budget".

6.    Use conditional formatting to change the text colour to red if the variance is less than 0 else change the text colour to green.

Tutorial exercise 2 

Write formulas that implement the above scenarios. 

Visualizing the data using charts

Charts are a great way of visualizing our data. We will now add a pie chart to our income sheet for the projected monthly income. The image below shows our data 

As marked in the above image our chart should only show the data for 

1.    Item column

2.    Subtotal column

Highlight items 1 to 5 in Item column 

Hold Ctrl button on the keyboard and highlight the subtotals from 1 to 5 in subtotals column. Your selection should look as shown in the image below 

Click on INSERT tab from main menu 

•    Use the charts ribbon bar, click on the pie chart drop down and select a pie chart of your choice.

•    Your pie chart should now look similar to the one shown below

Tutorial exercise three

Create charts for the actual expenditure, projected income, and actual income 

Download the above Excel File

Summary

In this tutorial, we have learnt and applied the knowledge gained in the previous tutorials. We have also been able to visualize our data using charts. 



4