Meet fellow New Skills Academy students in our study group. Join now!
Continue Course

Microsoft Excel Advanced Certificate

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


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


1
Module 1: Add a Password
20 minutes
2
Module 2 : AND Function
20 minutes
3
Module 3 : Auto Updating Drop-down Lists
20 minutes
4
Module 4 : CEILING Formula
20 minutes
5
Module 5 : Charts that Update Automatically
20 minutes
6
Module 6 : CHOOSE Formula
20 minutes
7
Module 7 : Compare 2 Lists with VLOOKUP Function
20 minutes
8
Module 8 : CORREL Function
20 minutes
9
Module 9 : Create an Advanced Calculator
20 minutes
10
Module 10 : Create a Combo Chart
20 minutes
11
Module 11 : Create a Custom Tab
20 minutes
12
Module 12 : Create a Pivot Chart
20 minutes
13
Module 13 : Create Visual In-cell Indicators
20 minutes
14
Module 14 : Create Visual In-cell Indicators with Icon Sets
20 minutes
15
Module 15: Customise the Status Bar
20 minutes
16
Module 16: DATEDIF Function
20 minutes
17
Module 17: DATEVALUE Function
20 minutes
18
Module 18: DAVERAGE Function
20 minutes
19
Module 19 : DCOUNT Function
20 minutes
20
Module 20 : DCOUNTA Function
20 minutes
21
Module 21 : Depreciation Formula
20 minutes
22
Module 22 : DGET Function
20 minutes
23
Module 23 : DMAX Function
20 minutes
24
Module 24 : DPRODUCT
20 minutes
25
Module 25: DSUM Function
20 minutes
26
Module 26: EDATE Function
20 minutes
27
Module 27: EOMONTH Function
20 minutes
28
Module 28: Find and Replace
20 minutes
29
Module 29: FIND Function
20 minutes
30
Module 30: INDEX Function
20 minutes
31
Module 31: INDEX MATCH Function
50 minutes
32
Module 32: INDIRECT Function
20 minutes
33
Module 33: Insert Serial Number Automatically
20 minutes
34
Module 34: IPMT Function
20 minutes
35
Module 35: ISBLANK Function
20 minutes
36
Module 36: ISERR Function
20 minutes
37
Module 37: ISLOGICAL Function
20 minutes
38
Module 38: ISNONTEXT Function
20 minutes
39
Module 39: ISNUMBER Function
20 minutes
40
Module 40: ISODD Function
20 minutes
41
Module 41: Isolate First Name
20 minutes
42
Module 42: ISTEXT Formula
20 minutes
43
Module 43: LEFT Function
20 minutes
44
Module 44: LEN Function
20 minutes
45
Module 45: MATCH Function
20 minutes
46
Module 46: VLOOKUP Multiple Results at Once
20 minutes
47
Module 47: OR Function
20 minutes
48
Module 48: PMT Function
20 minutes
49
Module 49: REPLACE Function
20 minutes
50
Module 50: Select all Cells with Comments
20 minutes
51
Module 51: Sparkline Charts in Excel
20 minutes
52
Module 52: Straight Line Depreciation
20 minutes
53
Module 53: Strikethrough
20 minutes
54
Module 54: Sum of Years Depreciation
20 minutes
55
Module 55: Timeline in Pivot Table
20 minutes
56
Module 56: VLOOKUP with 2 Criteria
20 minutes
57
Module 57: VLOOKUP with DropDown List
20 minutes
58
Module 58: VLOOKUP with MAX
20 minutes
59
Module 59: VLOOKUP with MIN
20 minutes
60
Module 60: Watch Window In Excel
20 minutes

Module 13 : Create Visual In-cell Indicators

data formatting

Visit Module

Module 29: FIND Function

29.1 FIND Function


You can use the FIND function in Excel to get the location of specific text inside a string of text.

 also find multiple instances of a letter. FIND can be used in conjunction with other logical formulas such as

 IF, ELSE, OR, AND, VLOOKUP, HLOOKUP and INDIRECT to look up data.

=FIND(find_text, within_text, [start_num})

 

Visit Module

Module 37: ISLOGICAL Function

Check if value inc ell is logical. ISLOGICAL part of IS group returning TRUE/FALSE. TRUE if data is logical -FALSE if not. Used with cell address to provide a value. Example is B4 logical based. TO DO =ISLOGICAL(value)-- value, the value to check. 

THIS CAN be used with IF to return specific data ( if the value is TEXT) 

Visit Module

Module 50: Select all Cells with Comments

Comments are a useful feature, stores info in a cell. commetted cells have a lil red dot in the corner. Some worksheets have plenty of comments this finds them and allows you the edit them. TO DO - 

Visit Module

Module 1: Add a Password

pass word 

Visit Module

Module 26: EDATE Function

26.1 EDATE Function


The EDATE function in Excel allows you to return the serial number of a date that is the specified amount of months after a set date.

 

You can then convert this into a date format.

 

The formula is written as: =EDATE(start_date, months)

 

Start_date - This is the start date that you would like to calculate the months from.

 

Months - The number of months you would like to calculate from the start date.

 

It is worth noting that the first date that can be used with this formula is January 1, 1900, as Excel does not use serial numbers for anything prior to this period. Any date before January 1, 1900 will only return an error.
Visit Module

Module 53: Strikethrough

As with other Microsoft Office programs and similar software or apps for data management and word processing, it is possible to Strikethrough text in Excel.
 
You can choose single cells, multiple cells, or even entire rows or columns. This is useful to note for a variety of reasons, such as when you may wish to disregard a comment within a document but not delete it entirely.
 
To use strikethrough, you can add it to your Quick Access Toolbar for easy access:-
 
Click the arrow next to the Quick Access Toolbar, choose More Commands
 
Click the drop-down to choose All Commands
 
Then scroll down to Strikethrough, select it and click Add, then OK.
 
You can then select the text you wish to put a line through and choose the Strikethrough button in the Quick Access Toolbar.
Visit Module

Module 2 : AND Function

So the cell is greater than 80, and cell > 80, and cell > 80 will either make the cell say true or false. 

=AND(D6>80,AND(E6>80),AND(F6>80) 

 

OR WITH IF

 

Visit Module

Module 3 : Auto Updating Drop-down Lists

Make the date you want a list from a table so you can add new infomation. Go to Data then date validation 

Visit Module

Module 5 : Charts that Update Automatically

Charts that Update Automatically

To make a chart update automatically you can select your existing list and format it as a table by going to the Home tab and clicking on Format as Table. Now you can add to the bottom of your source list and the data will automatically appear in your chart.

GOOGLE SHEET

Use Dynamic Ranges: If you expect new data to be added regularly, consider using functions like OFFSET or INDIRECT to define a dynamic data range. This way, the range will automatically adjust as you add new data.

  • Example: =OFFSET(A1,0,0,COUNTA(A:A),COUNTA(1:1)) – This formula will create a dynamic range starting from cell A1, expanding downward and rightward automatically as new data is added.
Visit Module

Module 6 : CHOOSE Formula

the first argument is where the cell is going to be placed in and the following arguments are values starting from the same first cell . The following arguments are where you want the results to be . this is a look up function like v and h

Visit Module

Module 7 : Compare 2 Lists with VLOOKUP Function

Comparing two lists in Excel using the VLOOKUP formula is a common task that can help you find matches or differences between these lists. Here’s a step-by-step guide on how to do it:

Step 1: Prepare Your Lists

  1. Arrange Lists: Ensure your two lists are in separate columns. For example, List 1 is in Column A, and List 2 is in Column B.

Step 2: Use the VLOOKUP Formula

  1. Formula Placement: Click on the cell where you want the comparison results to appear. This could be next to your first list (e.g., in Column C, next to List 1).

  2. Enter VLOOKUP Formula: The basic syntax of the VLOOKUP formula is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

    • lookup_value: The value in List 1 you want to find in List 2. For example, if you're starting with the first item in List 1, this would be A2.
    • table_array: The range that contains List 2. If your List 2 is in B1 to B100, then the range is B$1:B$100. The dollar signs ($) fix the range so that it doesn’t shift when you copy the formula down.
    • col_index_num: Since you’re looking up in a single column (List 2) and you want to return the matching value from this column, this number is 1.
    • [range_lookup]: Use FALSE to find an exact match.

  3. Complete the Formula: For instance, if you are comparing the value in cell A2 with values in column B (from B1 to B100), the formula would be =VLOOKUP(A2, B$1:B$100, 1, FALSE).

Step 3: Drag the Formula Down

  1. Copy Formula: After entering the formula in the first cell (e.g., C2), drag the fill handle (a small square at the bottom right of the cell) down to copy the formula to the other cells.

Step 4: Analyze the Results

  1. Interpreting Results: The VLOOKUP formula will return the matching value from List 2. If there’s no match, it will return an error (typically #N/A).
  2. Highlight Differences: Use Conditional Formatting to highlight cells in your results column that contain errors, indicating items in List 1 that are not in List 2.

Tips:

  • Data Consistency: Ensure that the data in both lists are consistent (e.g., no extra spaces, same case) to avoid missed matches.
  • Error Handling: You can wrap your VLOOKUP formula in an IFERROR function to handle errors more gracefully. For example: =IFERROR(VLOOKUP(A2, B$1:B$100, 1, FALSE), "Not Found"). This will display "Not Found" instead of an error if there’s no match.
  • Duplicate Values: VLOOKUP will return the first match it finds. If List 2 contains duplicates, it won’t necessarily find all instances.

By using the VLOOKUP formula, you can effectively compare two lists in Excel to identify matches or differences, which is especially useful in various data analysis tasks.

Visit Module

Module 8 : CORREL Function

 

Correl Formula

 

 

 

 

 

 

 

 

 

 

Month

Avg Temp

Air Cond.

Sales

 

Advertising

 Costs

Sales

Jan

20

100

 

£2,000 

£20,000 

Feb

30

200

 

£1,000 

£30,000 

Mar

30

300

 

£5,000 

£20,000 

Apr

40

200

 

£1,000 

£40,000 

May

50

400

 

£8,000 

£40,000 

Jun

50

400

 

£1,000 

£20,000 

 

 

 

 

 

 

 

Correlation

86%

 

Correlation

28%

 

 

 

 

 

 

 

 

 

 

 

 

This function examines two sets of data to determine the degree of relationship between the two sets

 

 

 

 

 

The result will be a decimal between 0 and 1.

 

 

 

 

 

The larger the result, the greater the correlation.

 

 

 

 

 

 

 

 

 

 

 

In Table 1, the Monthly temperature is compared against the Sales of air conditioning units.

 

 

 

 

 

The correlation shows that there is an 0.864 relationship between the data.

 

 

 
Visit Module

Module 52: Straight Line Depreciation

Microsoft Office Course says 100% complete but I am on Section 52.

Visit Module

Frequently Asked Questions

Anyone who has an interest in learning more about this subject matter is encouraged to take the course. There are no entry requirements to take the course.

The course is broken down into 60 individual modules. Each module takes between 20 and 90 minutes on average to study. Although you are free to spend as much or as little time as you feel necessary on each module, simply log in and out of the course at your convenience.

You can study the course any time you like. Simply log in and out of the web based course as often as you require. The course is compatible with all computers, tablet devices and smart phones so you can even study while on the move!

Once you have completed all 60 modules there is a multiple choice test. The questions will be on a range of topics found within the 60 modules. The test, like the course, is online and can be taken a time and location of your choosing.

The pass mark for the test is 70%.

If you don’t pass the test first time you will get further opportunities to take the test again after extra study. There are no limits to the number of times you can take the test. All test retakes are included within the price of the course.

You can either use your Visa, MasterCard, American Express, Solo cards or PayPal account to pay for the online course. Our site uses the latest SSL encryption to ensure your safety. All payments are handled securely by PayPal.

You can begin the course immediately after your payment has been received. You will create your login details during the checkout process. We will also send you an email confirming your login details.

We estimate that the course will take about 10 hours to complete in total, plus an additional 30 minutes for the end of course test.

Once you have been awarded your certificate it is valid for life. The certificate does not expire or need renewing.

Resources

Excel shortcuts for a Mac: Exceljet.net



Worksheets

Module 1: Add a Password

Module 1: Add a Password

Module 2 : AND Function

Module 2 : AND Function

Module 3 : Auto Updating Drop-down Lists

Module 3 : Auto Updating Drop-down Lists

Module 4 : CEILING Formula

Module 4 : CEILING Formula

Module 5 : Charts that Update Automatically

Module 5 : Charts that Update Automatically

Module 6 : CHOOSE Formula

Module 6 : CHOOSE Formula

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 8 : CORREL Function

Module 9 : Create an Advanced Calculator

Module 10 : Create a Combo Chart

Module 10 : Create a Combo Chart

Module 11 : Create a Custom Tab

Module 12 : Create a Pivot Chart

Module 12 : Create a Pivot Chart

Module 13 : Create Visual In-cell Indicators

Module 13 : Create Visual In-cell Indicators

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 15: Customise the Status Bar

Module 16: DATEDIF Function

Module 16: DATEDIF Function

Module 17: DATEVALUE Function

Module 17: DATEVALUE Function

Module 18: DAVERAGE Function

Module 18: DAVERAGE Function

Module 19 : DCOUNT Function

Module 19 : DCOUNT Function

Module 20 : DCOUNTA Function

Module 20 : DCOUNTA Function

Module 21 : Depreciation Formula

Module 21 : Depreciation Formula

Module 22 : DGET Function

Module 22 : DGET Function

Module 23 : DMAX Function

Module 23 : DMAX Function

Module 24 : DPRODUCT

Module 24 : DPRODUCT

Module 25: DSUM Function

Module 25: DSUM Function

Module 26: EDATE Function

Module 26: EDATE Function

Module 27: EOMONTH Function

Module 27: EOMONTH Function

Module 28: Find and Replace

Module 29: FIND Function

Module 29: FIND Function

Module 30: INDEX Function

Module 30: INDEX Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 32: INDIRECT Function

Module 32: INDIRECT Function

Module 33: Insert Serial Number Automatically

Module 33: Insert Serial Number Automatically

Module 34: IPMT Function

Module 34: IPMT Function

Module 35: ISBLANK Function

Module 35: ISBLANK Function

Module 36: ISERR Function

Module 36: ISERR Function

Module 37: ISLOGICAL Function

Module 37: ISLOGICAL Function

Module 38: ISNONTEXT Function

Module 38: ISNONTEXT Function

Module 39: ISNUMBER Function

Module 39: ISNUMBER Function

Module 40: ISODD Function

Module 40: ISODD Function

Module 41: Isolate First Name

Module 41: Isolate First Name

Module 42: ISTEXT Formula

Module 42: ISTEXT Formula

Module 43: LEFT Function

Module 43: LEFT Function

Module 44: LEN Function

Module 44: LEN Function

Module 45: MATCH Function

Module 45: MATCH Function

Module 46: VLOOKUP Multiple Results at Once

Module 46: VLOOKUP Multiple Results at Once

Module 47: OR Function

Module 47: OR Function

Module 48: PMT Function

Module 48: PMT Function

Module 49: REPLACE Function

Module 49: REPLACE Function

Module 50: Select all Cells with Comments

Module 51: Sparkline Charts in Excel

Module 51: Sparkline Charts in Excel

Module 52: Straight Line Depreciation

Module 52: Straight Line Depreciation

Module 53: Strikethrough

Module 53: Strikethrough

Module 54: Sum of Years Depreciation

Module 54: Sum of Years Depreciation

Module 55: Timeline in Pivot Table

Module 56: VLOOKUP with 2 Criteria

Module 56: VLOOKUP with 2 Criteria

Module 57: VLOOKUP with DropDown List

Module 57: VLOOKUP with DropDown List

Module 58: VLOOKUP with MAX

Module 58: VLOOKUP with MAX

Module 59: VLOOKUP with MIN

Module 59: VLOOKUP with MIN

Module 60: Watch Window In Excel

Module PDF's

Module 1: Add a Password

Module 2 : AND Function

Module 3 : Auto Updating Drop-down Lists

Module 4 : CEILING Formula

Module 5 : Charts that Update Automatically

Module 6 : CHOOSE Formula

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 8 : CORREL Function

Module 9 : Create an Advanced Calculator

Module 10 : Create a Combo Chart

Module 11 : Create a Custom Tab

Module 12 : Create a Pivot Chart

Module 13 : Create Visual In-cell Indicators

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 15: Customise the Status Bar

Module 16: DATEDIF Function

Module 17: DATEVALUE Function

Module 18: DAVERAGE Function

Module 19 : DCOUNT Function

Module 20 : DCOUNTA Function

Module 21 : Depreciation Formula

Module 22 : DGET Function

Module 23 : DMAX Function

Module 24 : DPRODUCT

Module 25: DSUM Function

Module 26: EDATE Function

Module 27: EOMONTH Function

Module 28: Find and Replace

Module 29: FIND Function

Module 30: INDEX Function

Module 31: INDEX MATCH Function

Module 32: INDIRECT Function

Module 33: Insert Serial Number Automatically

Module 34: IPMT Function

Module 35: ISBLANK Function

Module 36: ISERR Function

Module 37: ISLOGICAL Function

Module 38: ISNONTEXT Function

Module 39: ISNUMBER Function

Module 40: ISODD Function

Module 41: Isolate First Name

Module 42: ISTEXT Formula

Module 43: LEFT Function

Module 44: LEN Function

Module 45: MATCH Function

Module 46: VLOOKUP Multiple Results at Once

Module 47: OR Function

Module 48: PMT Function

Module 49: REPLACE Function

Module 50: Select all Cells with Comments

Module 51: Sparkline Charts in Excel

Module 52: Straight Line Depreciation

Module 53: Strikethrough

Module 54: Sum of Years Depreciation

Module 55: Timeline in Pivot Table

Module 56: VLOOKUP with 2 Criteria

Module 57: VLOOKUP with DropDown List

Module 58: VLOOKUP with MAX

Module 59: VLOOKUP with MIN

Module 60: Watch Window In Excel

Chat with students on your course
Get support and advice
Exclusive access for New Skills Academy students
Win study group prizes and be the first to hear about new offers
Join Now