Microsoft Excel for Beginners Certification
Module 1: Introduction to Excel
Duration: Approx 3 minutes
Welcome to your Excel for Beginners Course
Before you begin the course, please read these important notes
Modules
The course is broken down into 36 modules. You are free to spend as long as you like on each module. Once you have finished the module, click the “Next” button at the bottom of the page to move to the next one.
Worksheets and Exercises for Completion
Worksheets: At the end of the modules you will find a Worksheet that will test your learning of the module.
Additional Exercises: Also available are additional exercises that you can use to practice the functions. Click on the link below to download the instructions and spreadsheets:
- Excel for Beginners Exercises - Student Instructions
- Excel for Beginners Exercises - Spreadsheets (Budget, Customers, Dates/Numbers, Inventory, Print, Sales, Suppliers, Training)
Please read the instructions carefully for these as you will need to save your spreadsheets upon completion of the exercises, for use throughout the course.
We also provide copies of the completed Excel exercises, in case you wish to refer to them for help/guidance:
End of Course Test
At the end of the course, there is a 30-question test. Each question has multiple-choice answers and you will have 3 options to choose from, one of them is correct. In order to pass the test, you need to answer 70% (21/30) of the questions correctly. Don't panic if you don't pass the first time. You can take the test as many times as you need. If you do not pass on the first occasion, we recommend that you review the modules before taking the test again.
Completion of the Course
Once you have successfully completed the end-of-course test, you will be awarded your certification. You can download and print your certificate any time you want. If you would prefer us to send you a hard copy of your certificate by mail, we can do so for a small charge.
What you can do once you have completed the course
Once you have completed the course, you will have a great understanding of the basic concepts of Excel. You can then move on to some more complex functions with our Intermediate Excel Course.
Support
If you need any assistance, we are here to help you. Simply click on the support menu (located on the left of the page) and one of our support staff will get back to you as soon as possible. Please note, support hours are 9:00 - 17:30 Monday - Friday (we have limited support outside of these hours and your enquiry may take longer to respond to).
Let's Get Going
We hope you enjoy your course and wish you the best of luck with your future career. You can now begin your course.
1.1 Introduction
Microsoft Excel is one of the most widely used programs for visualizing, organizing and analyzing data.
Excel is used by a wide range of businesses and professionals, including accountants, financial analysts, data analysts, project managers, business analysts, and marketing professionals.
It can be used for budgeting, tracking expenses, data visualization, tracking hours, managing stock levels, analyzing customer data, project management, and innumerable other tasks.
It is also used by individuals to manage their own personal finances, plan meals, track their fitness, plan events, and so on.
Below are a few terms used in Excel and what they mean:
Cells
Cells are the basic building blocks of an Excel worksheet. Each cell has a unique address called a cell reference, which is a combination of the column letter and row number (e.g., A1, B2, C3, etc.). Cells can contain various types of data, including numbers, text, dates, and formulas.
Formulas
A formula is a set of instructions that performs a calculation on one or more values in Excel. Formulas always start with an equals sign (=) and can include mathematical operators (e.g., +, -, *, /), cell references, and functions (e.g., SUM, AVERAGE, MAX, MIN).
Functions
Functions are built-in formulas that perform specific calculations or tasks in Excel. Some common functions include SUM (adds a range of numbers), AVERAGE (calculates the average of a range of numbers), MAX (finds the maximum value in a range), and MIN (finds the minimum value in a range). Functions are typically used in formulas and can take arguments (i.e., inputs) in parentheses.
Relative and absolute cell references
When creating formulas in Excel, it is essential to understand the difference between relative and absolute cell references. Relative cell references adjust based on the location of the formula when copied or moved, whereas absolute cell references remain fixed. You can make a cell reference absolute by adding a dollar sign ($) before the column letter or row number (e.g., $A$1).
AutoFill
Excel's AutoFill feature is a quick and easy way to fill a series of cells with a pattern or sequence of values. For example, if you type "1" and "2" in two adjacent cells, you can use AutoFill to fill the remaining cells in the series with the numbers "3", "4", "5", etc.
Formatting
Excel offers many options for formatting cells to enhance their appearance and improve readability. You can change the font style and size, apply bold or italic formatting, adjust the alignment, add borders or shading, and much more. Formatting can also be applied to entire rows, columns, or worksheets.
These are just a few of the basic concepts in Excel. As you become more comfortable with the program, you can explore more advanced features and functions to create even more sophisticated spreadsheets.
- Calculating sums, averages and totals
- Counting cells
- Learning formulas, such as IF, COUNTIF, SUMIF, VLOOKUP, HLOOKUP and AVERAGEIF
- Pivot tables
- Inserting, editing and deleting comments
- Creating borders
- Wrapping text
- Merging cells
- Pasting, sorting, and converting columns to rows
- Creating graphs and charts
- Using the dictionary
- Using hyperlinks
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Learning Topics
Module 7: Percentage
7.1 Intro Percentage
To display percentage format thats in decimal format, click on the cell, cloumn, or row where you want data to show in percentage format. Click on home tab and the number group and click on the (%) sign, data should show in percent.
Module 16: IF Formula
16.1 IF Formula
Can be used to evaluate text and values and evaluate errors.
=IF(B6>C6,”Over budget”,”Within budget”)
To decide who has done better in results
=if highlight cell > highlight comparison cell,"this person has done better","other person has done better"
Module 14: AVERAGEIF
14.1 Intro Averageif(range,criteria,[average_ range)
The range should refer to the range of cells you want to be included in the calculation.
Module 1: Introduction to Excel
Excel
It can be used for budgeting, tracking expenses, data visualization, tracking hours, managing stock levels, analyzing customer data, project management, and innumerable other tasks.
It is also used by individuals to manage their own personal finances, plan meals, track their fitness, plan events, and so on.
Module 2: Add
ADDING - There are two important elements you cannot forget: First, it is important that you put the equals sign (=) before the operation. Second, the sign to add is the plus (+). Place the plus between the two numbers or cell references you want to add, and press enter.
Module 9: Print
Module 10: Paste Special
Sometimes, in addition to copying and pasting data, you want to paste the format, column width or others into the sheet.
Module 15: SUMIF Formula
To exclude certain criteria:
=Sum if range of cells, "<>name to be excluded from sum", range of value cells e.g units sold
To add cells that are less than < or equal to 50.
=Sum if highlight range of cells of units sold "<=50"
Sum of all units that are not equal to 50
=Sumif highlight range of cells of units sold,"<>50"
Find number of units sold that were less than 50.
=Sumif highlight units sold, "<50", highlight cells of total amount sold
Find total value sold of only 50 units
=Sumif highlight range of units sold,highlight cell showing 50 units, highlight total value range of cells.
Module 17: VLOOKUP
17.1 Intro VlookUp
To find information from another table
=vlookup"what name you want to look for", highlight information cells on table you wish to search, which column on the table do you wish to look for, exact match.
Module 3: Multiply
To use this, type the formula:=PRODUCT(cell or range of cells1,cell or range of cells2)
Module 18: HLOOKUP
vlook formula is checking dates based on columns
hlook formula is checking dates based on rows
Module 19: Countif Formula
19.1 Intro CountIF
count if formula is counting how many times apper a name of a number for example in one range (column)
Module 4: Subtraction
4.1 Intro Subtraction
=10-5and enter and (5) will apper in cell. Youcan select cells you want to subtract rather than type the values. Doing this the formula will appear something like Exp (=E5-F5) pressing enter the value will a appear.
Module 6: Sort
6.1 Intro Sort
Sorting in excel helps with effective visualization, analysis, and understanding.
Sort data by text, select column of data with text or make the active cell is in a table column withdata and text you want sorted. Click Data Tab, click Fort & Filter Group. click on button to sort from A to Z or Z to A.
To sort numbers same applies with data by text. Data Tab, Sort & Filter group organize from smallest to largest or largest to smallest by clicking on A to Z.
Sort dates and times, A to Z button will sort from oldest date or time to most recent date or time and Z to A button will do the same but in reverse.
Module 8: Running Totals
8.1 Intro Running Totals
Using total function your worklsheet or tablet have at least, in general, a description of transaction, column with numerical data which you want to apply the running total such as ( deposits and withdrawals or product vaule.) and destination column while running totals output information will go (Which could be a total blanace or total inventory value).
Click a blank cell where you want the running total, Follow formula: =SUM(x value cell: current value cell) =SUM($C$2:$C2.) ($) means the value wont shift once you copy the formula itll always refer to that row of column.
Copy the formula cells into running totalcolumns, itll automatically calculate the running total items in inventory. If you dont want to manually type ($) you can click on the relevant part of the formula and press F4 or (Fn+F4 or command T on Mac pc.) Everytime you press (F4) itll change where the ($) is placed, eventually going back to defualt formula with no ($) sign.
Module 11: Merge
11.1 Intro Merge
Note cells cant be split, but you can use the merge function to make cells appear above and below one another,or left and right across from each other upon merge cells being split. When mereging cells, cells become larger and are displayed across multiple row columns.
Merging Cells: Select two or more cells you want to merge. If one cell have text the others cant.
After selecting paierd cells you want to merge find the aligment group in HOME TAB and click merge & center. Thatll automatically merge cells.
Unmerging cells after merging them, hit merge button in the Alignment group of the HOME TAB will have a unmerge cells option whcih will bring them back to normal size.
Module 12: Filter
12.1 Intro Filter
Filtered data will only display rows that you have specified. Once you have data filtered you can copy, edit, print, and format the subset of data yo creadted.
Most common way to filter is by using the AUTOFILTER Function, there 3 types of filters you filter by list values, format, or by specified criteria.
To use this filiter function, click on a range of cells with data you want filtered, then hit DATA TAB and hit SORT & FILTER gruop then click on the fliter icon. Arrow will appear on the column header, and when you click on the arrow you can select the criteria you want to use the filter data for.
You can also filter by dates, times, numbers, top or bottom numbers, blanks or non blanks, text, cell color, font color, icon set, or selection.
NOTE: If you want to filter by more than one criteria, select AND, and filter the column or selection with either or both criteria, you can select OR.
To clear filter, on DATA TAB, in SORT & FILTER gtoup,click CLEAR.
Module 13: Average
13.1 Intro Average
Average is useful for finding the age of respondents, or how long a person has been working for a company.
Formula to find average is: =AVERAGE (range) or =AVERANGE (#1,#2, OR #3)
If a range or cell refernce has text, logical values, or empty cells, they will be ignorded. but cells with value (0) will be included.
Module 20: CountBlank
20.1 Intro Count Blank
CounterBank function can help you determine how much info is needs to be filled out.
This function counts empty formulas and ones that arent filled out yet. Also cells with a zero value are counted.
For thsi function to opreate (iterative function) needed to be turned off. How to click FILE, hit OPTIONS, then FORMULA, and under CALCULATON OPTIONS, enable (iterative calcluations box is off.
Module 21: Counta Formula
21.1 Intro COUNTa
Counts the # of cells that aren't empty inrange.
They're two values to find cells in two seperate sets of data. it can process data and text.
Module 22: Count Formula
22.1 Intro COUNT
Count the numbers of cells that containe #'s within the list of arguments.
It dosent matter whats sort of data is contained in cell, it includes numbers, data, and others.
Module 23: Concatenate
23.1 Intro CONCATENATE
Concatenate joins two or more text strings into one string.
it can make full sentences by joning data,or join first and last names in dfferent columns.
First text or input is required, from second one forward are optional for man of 255 items.
Module 24: Transpose
24.1 Intro TRANSPOSE
Transpose can switch or reroute cells.
Do this by copying, pasting, and using transpose option which will creat a duplicated/static data. Any letter changes to data will not pull thorugh to transose data int he original columns.
Alt, you can type formula using transpose function which will switch the arrangement of cells from virtical to horizontal but its dynamic and will change capture to original data.
Module 25: Date Formula
25.1 Intro DATE FUNCTON
Is useful when you need to take 3 vakues presented separately and combine them to form a date.
Reminder all three needs to be presented as numbers for it to return a date. Exp (2015,4,6) April 6,2015.
Frequently Asked Questions
To progress on to the next module of your course scroll to the bottom of the current module and press the “NEXT” button. This will take you directly to the following module.
Once you have completed the last module of the course please click on the button at the bottom of the module that says “START”. This will take you to the test.
The pass mark for the end of course test is 70%
Yes, you can re-take your test as many times as you need to.
No, once you have passed a test it is not possible to retake it.
When logging on to your course directly through our site you have lifetime access.
Click on the “My Profile” button at the top of any page on our site. This will take you to your profile page. Once there scroll down to the section headed “Edit Your Profile” and enter your new password.
Please click here to contact our student support team
If you would like to go back and view a completed module you can do this by clicking the “Course Modules” option from the side bar menu of your course. This will take you to a list of the modules contained within your course. Simply select the module you wish to study again.
Please click here to find out how to save your course modules as PDF files