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
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 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.
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.
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
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
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
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
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
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
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
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
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
• 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.
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 |
1 | Addition (+) | 13 | 3 | 16 |
2 | Subtraction (-) | 21 | 9 | 12 |
3 | 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.
Using the knowledge gained in the above example, try to write the formulas for subtraction, division, and multiplication.
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.
• 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
• 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
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.
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
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.
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).
In this tutorial, we are going to cover the following topics.
• Data validation
• Data filters
• Group and Ungroup
• Adding images to spreadsheets
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 |
1 | Jane | |
2 | James | |
3 | 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 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.
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
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.
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
For this tutorial, we will work with the following datasets.
Home supplies budget
S/N | ITEM | QTY | PRICE | SUBTOTAL | Is it Affordable? |
1 | Mangoes | 9 | 600 | ||
2 | Oranges | 3 | 1200 | ||
3 | Tomatoes | 1 | 2500 | ||
4 | Cooking Oil | 5 | 6500 | ||
5 | Tonic Water | 13 | 3900 |
House Building Project Schedule
S/N | ITEM | START DATE | END DATE | DURATION (DAYS) | |
1 | Survey land | 04/02/2015 | 07/02/2015 | ||
2 | Lay Foundation | 10/02/2015 | 15/02/2015 | ||
3 | Roofing | 27/02/2015 | 03/03/2015 | ||
4 | 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
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.
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
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
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.
Let's look at some of the most commonly used functions in Excel. We will start with statistical functions.
S/NFUNCTIONCATEGORYDESCRIPTIONUSAGE
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()
As the name suggests, these functions operate on numeric data. The following table shows some of the common numeric functions.
S/NFUNCTION | CATEGORY | DESCRIPTION | USAGE | |
1 | ISNUMBER | Information | Returns true if the supplied value is numeric and false if it is not numeric | =ISNUMBER(A3) |
2 | RAND | Math & Trig | Generates a random number between 0 and 1 | =RAND() |
3 | ROUND | Math & Trig | Rounds off a decimal value to the specified number of decimal points | =ROUND(3.14455,2) |
4 | MEDIAN | Statistical | Returns the number in the middle of the set of given numbers | =MEDIAN(3,4,5,2,5) |
5 | PI | Math & Trig | Returns the value if Pi | =PI() |
6 | POWER | Math & Trig | Returns the result of a number raised to a power | =POWER(2,4) |
7 | MOD | Math & Trig | Returns the remainder when you divide two numbers | =MOD(10,3) |
8 | ROMAN | Math & Trig | Converts a number to roman numerals | =ROMAN(1984) |
These functions are used to manipulate text data. The following table shows some of the common string functions.
S/NFUNCTIONCATEGORYDESCRIPTION | 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) | ||
5 | FIND Text | Returns the starting position of a text string within another text string. This function is case-sensitive. | =FIND("oo","Roofing",1) |
6 | REPLACE Text | Replaces part of a string with another specified string | =REPLACE("Roofing",2,2,"xx") |
These functions are used to manipulate date values. The following table shows some of the common date functions
S/NFUNCTION | 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") |
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
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.
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.
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
The following table shows all of the logical functions in Excel
S/NFUNCTIONCATEGORYDESCRIPTION | 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 |
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.
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
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 |
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 |
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.
• 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
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
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.
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
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
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.
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
• Rename the Sheet1 to Income
• Enter the data shown below
• Projected Monthly Income
S/N | Description | Amount ($) |
1 | Salary | 600.00 |
2 | Freelance works | 250.00 |
3 Grand Total Expected annual income: | Others | 180.00 |
Actual Income
S/N | Description | Amount ($) |
1 | Salary | 600.00 |
2 | Freelance works | 200.00 |
3 Grand Total Actual annual income: | Others | 150.00 |
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
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.
Add a new sheet and rename it to Expenditure
Enter the data as shown below
Budget
S/N | Item | Qty | Price | Subtotal | ||
1 | Rentals | 12 | 210.00 | |||
2 | Bills | 12 | 100.00 | |||
3 | Groceries | 12 | 230.00 | |||
4 | School | 2 | 500.00 | |||
5 | Miscellaneous | 6 | 133.00 |
Grand Total:
Expected Annual savings:
Actual income
S/N | Description | Qty | Price | Subtotal |
1 | Rentals | 12 | 210.00 | |
2 | Bills | 12 | 145.00 | |
3 | Groceries | 12 | 240.00 | |
4 | School | 2 | 500.00 | |
5 | Miscellaneous | 6 | 233.00 | |
Actual Annual Savings: | Grand Total: |
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.
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
Create charts for the actual expenditure, projected income, and actual income
Download the above Excel File
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.