Microsoft Excel Intermediate Certificate

Module 34: Use Themes
Duration: Approx 20 minutes
34.1 Use Themes
It is possible to change the colour themes for the formatting options in Excel to create a personalised look to your workbook.
34.2 Video Instruction
Use Themes in Excel – 1m 08s
34.3 Task
Estimated Time: 10 minutes
Download the worksheet that accompanies this video by clicking below. Once downloaded practise using Themes in Excel using the video above as a reference.
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 27: Change Text To Proper Case
To change the capitalisation of certain text, you can use a formula instead of wasting time deleting and retyping everything.
- Proper case refers to capitalising the first letter in a string and any letters that follow are lower case, like you might want to do for a title or name.
- To do this, first you have to create a column or row for the return value or information.
- Then input the formula as follows: =PROPER(reference value)
- This will change all of the text to proper case.
27.1 Change Text To Proper Case
To change the capitalisation of certain text, you can use a formula instead of wasting time deleting and retyping everything.
Module 6 : CHAR Function
The CHAR function (CHAR is for character), returns the character specified by a number.
It helps people label letters with a number by selecting the equal sign and typing in Char, by dragging it down it will label all numbers with letters.
6.1 Char Formula
The CHAR function (CHAR is for character), returns the character specified by a number.
The number refers to the character set used by your computer, and is up to number 255. For example, =CHAR(65) results in A on almost all computers.
Module 4 : Data Validation with List Feature
Module 12 : AVERAGEIFS Function
12.1 AVERAGEIFS Function in Excel
The AVERAGEIFS function is similar to the AVERAGE function in that it returns the arithmetic mean of a particular set of numbers.
The average range is the cells you want to be taken into account for the analysis.
Module 1: SumProduct Formula
This function will multiply corresponding components in the given arrays, and will then return the sum of the products.
Array1 is the only one that is required. This is the first argument (two columns of numeric data) whose components you want to multiply and then add together.
Module 9 : EXACT Function
This function takes into account lower and upper case letters, but it ignores differences in formatting.
Text1 refers to the first text string and text2 refers to the second text string to which the first one is being compared and vice versa.
The text1 and text2 references usually refer to cells, though it isn't necessary that the cells be right next to each other, visible, or even on the same sheet.
Module 10 : AVERAGEA Function
AVERAGEA is very similar to the AVERAGE function, in that it returns the arithmetic mean, or average, of a list of numbers.
The difference between the AVERAGEA and the AVERAGE formula is that: logical values, within arrays or reference arguments are counted, and text is counted as zero.
AVERAGEA counts logical values and ignores text?
Where as AVERAGE includes everything
Module 19 : N Function
The N function allows you to obtain a number that was converted from a non-interger.
e.g. of non interger= date/percentage/true/false etc
e.g. 23/sept/1978= 28756
e.g. TRUE=1
e.g. FALSE=0
19.1 N Function
The N function allows you to obtain a value converted to a number.
The value refers to the value you want converted.
Module 20 : RANDBETWEEN Function (Insert Random Numbers)
he formula for the RANBETWEEN function is as follows: =RANDBETWEEN(bottom, top)
20.1 RANDBETWEEN Function
The RANDBETWEEN function returns random integer numbers between minimum and maximum limits you specify.
Bottom refers to the smallest integer, or minimum, you want the function to return.
Another function (not discussed in the video) is =RAND(), which will return a random number between 0 and 1.
Module 7 : CLEAN Function
CLEAN FUNCTION-- import texts from other programs, the documents or worksheets contain characters that may not print
FORMULA
=CLEAN(cell reference)
7.1 Clean Function
The CLEAN function removes all characters that cannot be printed.
This will remove any potentially unreadable characters, or characters that cannot be printed.
Get rid of unwanted characters select cell and input formula =CLEAN()
Module 8 : CODE Function
8.1 code function
- the code function returns a numberic code for the first character used in a text string, or to one sole character
- this refers to the character set used by the computer, in case you don't have access to the full character set
- e.g. =CODE("text")
CHAR and CODE are opposite functions?
CHAR- associate a number to a certain character
CODE: relate/ associate a character to a number which is stuck to it based on the operating system
Module 18: MODE Function
if theres no repeated numbers the answer to the formula will say N/A
Formula--- =MODE(number1,number2)
18.1 MODE Function
The MODE function will return the most repeated value in an array or range of data.
Here, number 1 is required but any additional numbers are optional. They can also include ranges of data. Keep in mind that the data the cells are referring to could include names, arrays, or references that contain numbers. If an argument doesn't have any numbers, the MODE function will return 0.
Module 21 : RANK Function
=RANK(D6,D6:D14,0/1)
in the form of =RANK(number,ref[order]).
0=descending order (biggest---smallest)
1=ascending order (smallest---biggest)
if you add "$" sign it will be a fixed range
=RANK(number,$D$6:$D$14,0/1)
the rank would change each time the number/cell reference changes
21.1 RANK Function
The RANK function returns the rank of a number being referred to in a list of values, taking into account the size of the number relative to other values in a list.
Number refers to the number whose rank you want to find with the RANK function.
Module 22 : Remove Duplicate Values
Rather than deleting them one by one you can remove duplicate values using an Excel function.
To do this, select a cell or a range of cells.
Then go to the Data tab, and find the Data Tools group. Here, click Remove Duplicates.
Then, under Columns you can select one or more columns where you would like data removed.
You can select all columns by clicking Select All. You can clear all columns by clicking Unselect All.
Then click OK.
Module 23 : ROUND Function Up/Down
ROUND FUNCTION UP/DOWN
decimal values are not significant to you, so you can use the Round a Number function in Excel.
This will allow you to: round a number up, round a number down, round a number to the nearest number or round a number to a near fraction.
FORMULA-- =ROUNDUP(argument1, argument 2)
FORMULA-- =ROUNDDOWN(argument1, argument 2)
FORMULA-- =ROUND(argument1, argument2)
**argument 1= number which you want to round
**argument 2= how many decimal places to round it to
e.g. 7.12 (aka B6)
=ROUNDUP(B6,1)= 7.2 (rounding up by 1 d.p)
23.1 ROUND Function Up/Down
Sometimes decimal values are not significant to you, so you can use the Round a Number function in Excel.
This will allow you to: round a number up, round a number down, round a number to the nearest number or round a number to a near fraction.
The formula for rounding a number up is:
=ROUNDUP(argument1, argument2)
The first argument is the number to be rounded.
The second argument is the number of decimal places to which you want to round the result (0 is valid if you only want whole numbers).
The formula for rounding down a number is:
=ROUNDDOWN(argument1, argument2)
The first argument is the number to be rounded.
The second argument is the number of decimal places to which you want to round the result (0 is valid if you only want whole numbers).
To round a number to the nearest number or nearest fraction, you can use the following formula:
=ROUND(argument1, argument2)
The first argument is the number to be rounded.
The second argument is the number of decimal places to which you want to round the result (0 is valid if you only want whole numbers).
The videos below discuss Rounding Up and Rounding Down.
Module 25: Change Text To Lower Case
- To do this, first you have to create a column or row for the return value or information.
- Then input the formula as follows: =LOWER(reference value)
- This will change all of the text to lower case.
25.1 Change Text To Lower Case
To change the capitalisation of certain text, you can use a formula instead of wasting time deleting and retyping everything.
Module 26: Change Text To Upper Case
- To do this, first you have to create a column or row for the return value or information.
- Then, input the formula as follows: =UPPER(reference value)
- This will change all of the text to upper case.
26.1 Change Text To Upper Case
To change the capitalisation of certain text, you can use a formula instead of wasting time deleting and retyping everything.
Module 28: Insert Special Character
- (E.g.) copyright symbol (©)
- To insert a symbol on a worksheet, click the cell where you want the symbol inserted.
- Then, on the Insert tab, find the Text group and click on Symbol (use the Symbol dialog box)
- A symbol dialog box will appear, and make sure you are in the Symbols tab.
- Then, from the list of symbols, click the symbol you want to insert and click Insert.
28.1 Insert Special Character
Some symbols and special characters are not available on the keyboard.
Module 29: Use Autofill : A Time Saving Trick
AUTOFILL
will automatically fill in data that follows a pattern, which will save you time when inputting repetitive numbers, words, or strings of words.
One way is just by highlighting the table.
- Put something in the cell then extend it by highlighting the table
- Go to the Home Tab...Fill...Series
- Choose the Column/Rows
- Under type click on the linear
- Step Value =1
Shorten Long Sentences
- Highlight the very long sentence
- Highlight up to the column you want it to end at
- Select the Fill...and click Justify
29.1 Use Autofill
The Autofill function will automatically fill in data that follows a pattern, which will save you time when inputting repetitive numbers, words, or strings of words.
Module 30: Treemap Chart
- A Treemap chart lets you view your data in a hierarchical manner
- Easy to identify patterns in terms of popularity, prices, volume, and others.
- Treemap charts divide up categories into different sized rectangles that are proportional to the amount or number (units) they represent.
METHOD
- First need to input your data where you organise larger (left) to smaller (right) categories.
- When you have finished inputting your data, highlight the table, click the Insert tab, then click Insert Hierarchy Chart and finally Treemap.
- Colours will automatically be selected for you, but you can format and personalise these in the Format.
- You can also format how the labels appear by clicking Format, Series Options, Label Options, then choosing the Options you would like.
30.1 Treemap Chart
A Treemap chart lets you view your data in a hierarchical manner, which makes it easy to identify patterns in terms of popularity, prices, volume, and others.
Module 31: Sunburst Chart
- The Sunburst chart is useful when you want to display hierarchical data.
- The outer rings represent categorical divisions (contributing pieces of inner ring divisions).
- The innermost circle is the top of the hierarchy and it demonstrates how outer rings are related to inner rings.
(e.g) Quarterly Sales
The quarters would go in the middle, then the months, then the weeks on the outermost layer.
METHOD
- First you must select your data, which must be hierarchical.
- After you have created your data, select it, click Insert, then Insert Hierarchy Chart, then Sunburst Chart.
- Use the Design and Format tabs after clicking anywhere in your chart to customise how your chart looks.
31.1 Sunburst Chart
The Sunburst chart is useful when you want to display hierarchical data.
Module 32: Use Tables
TABLES
Tables in Excel allow you to easily manage and analyse information in a group of related data.
TABLE TIPS
- Select Data -- click cell and CTRL+A
- Create Headers -- Home tab...Format as table (my table has headers)
- Data range -- already selected if you highlighted data (if not write it like this e.g. $B$4:$J$35).
- There is a filtering option by headers, to help find specific data.
- Click cell... table design tab... summarize with pivot table...new worksheet
- Click cell...table design tab...remove duplicates (CTRL+Z to undo)
- To get AVERAGES/TOTALS/PERCENTAGES etc we can right click on arrow by total... click quick analysis and choose options of other functions for all rows.
TOTAL ROW -- If the Total Row doesn't show up, bring up the Table Tools by clicking in the table, and click on the design tab.
You will then see check boxes in Table Style Options. Click Total Row so that it appears at the bottom of your table.
32.1 Use Tables
Tables in Excel allow you to easily manage and analyse information in a group of related data.
Module 33: Use Templates
TEMPLATES
- Go to Files...Go to New... use whichever template you find the most useful.
- You can search for which template will help you.
Common templates include: personal and business budget, cash flow, calendars, to-do list, travel expenses, loan comparison calculators, schedules, invoices, inventories and more
Module 5 : ABS Function
5.1 ABS Function
The ABS Function will return the absolute value of a number in the location you desire.
The number can be typed in, or it can refer to a cell.
Module 11 : AVERAGEIF
AVERAGEIF
The AVERAGEIF function in Excel is similar to the AVERAGE function in that it calculates the arithmetic mean of a set of values, only of values that meet certain criteria.
For example, you can specify that it should only take into account values greater than 30 or 100.
The formula for the AVERAGEIF function is:
=AVERAGEIF(range, criteria, [average_range])
The range should refer to the range of cells you want to be included in the calculation.
Insert the relevant criteria needed to find the average.
average_range is optional, although you could specify even further what cells to take into account.
Module 13 : CELL Function With 10 Variations
CELL FUNCTION (with 10 variations)
The CELL function is an informative formula that tells you about the contents, location, formatting, and properties of a specific cell.
The formula for CELL is:=CELL(“what you want to find out about the cell”, cell location)
The variations of this formula include the following:
=CELL(“address”, cell location), to find the location of the cell.
=CELL(“col”, cell location), to find the column number.
=CELL(“row”, cell location), to find the row number.
=CELL(“type”, cell location), where b is for blank, l is for text, and v is for value.
=CELL(“prefix”, cell location), where it will return a ' for left, ^ for centre, and “ for right. When there is nothing, it will be displayed as numeric entries.
=CELL(“width”, cell location), to find out the width of a cell.
=CELL(“format”, cell location), which will tell you the number format of the cell. It is necessary to download the number codes for format for future reference.
=CELL(“parentheses”, cell location), where 1 is yes and 0 is no.
=CELL(“color”, cell location,) will tell you if the cell is formatted for coloured negatives. It will return 1 for yes, 0 for no.
=CELL(“protect”), will tell you if the cell is locked (1) or unlocked (0). =CELL(Filename, D5), will tell you the name of the file.
Module 14 : LARGE Function
LARGE Function
This function will return the Kth largest value in a previously defined data set.
You can use this function to select a value based on its standing when compared to the other values, including the highest score, second-place or third-place score.
The formula for the LARGE function is the following:=LARGE(array,k)
The array refers to the range of data in which you want to determine the largest value.
K refers to the position in the array or cell range of data to return (for example, if you want to return the second largest number from an array of values, you would type 2 for K). The result will be the value that fits that description.
Module 15: MAX Function
MAX Function
The MAX function returns the largest value in a set of values.
The formula for the MAX function is as follows: =MAX(number1, number 2,…)
Here, number 1 is required but any additional numbers are optional. Ranges of data are also valid.
Keep in mind that the data cells are referring to could include names, arrays, or references that contain numbers. If an argument doesn't have any numbers, the MAX function will return 0.
Module 16: MEDIAN Function
MEDIAN function
The MEDIAN function returns the median number of a set of numbers.
The formula for the MEDIAN function is as follows: =MEDIAN(number1, number 2,…)
Here, number 1 is required but any additional numbers are optional. Ranges of data can also be included. Keep in mind that the data the cells are referring to could include names, arrays, or references that contain numbers. If an argument doesn't have any numbers, the MEDIAN function will return 0. There is a maximum of 255 numbers that can be input.
Module 17: MIN Function
y17.1 MIN Function
The MIN function returns the smallest value in a set of values.
Here, number 1 is required but any additional numbers are optional. They can also include ranges of data.
Module 24 : Add Custom Background Image
24.1 Add Custom Background Image
In Excel you can add backgrounds into sheets for display purposes. These background images aren't printed, rather it is only visible on the screen.
Module 34: Use Themes
34.1 Use Themes
It is possible to change the colour themes for the formatting options in Excel to create a personalised look to your workbook.
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
Worksheets
Module 2 : Data Validation With Date
Module 2 : Data Validation With Date
Module 3 : Data Validation with Whole Numbers
Module 4 : Data Validation with List Feature
Module 4 : Data Validation with List Feature
Module 12 : AVERAGEIFS Function
Module 12 : AVERAGEIFS Function
Module 13 : CELL Function With 10 Variations
Module 13 : CELL Function With 10 Variations
Module 20 : RANDBETWEEN Function (Insert Random Numbers)
Module 20 : RANDBETWEEN Function (Insert Random Numbers)
Module 22 : Remove Duplicate Values
Module 23 : ROUND Function Up/Down
Module 23 : ROUND Function Up/Down
Module 23 : ROUND Function Up/Down
Module 23 : ROUND Function Up/Down
Module 24 : Add Custom Background Image
Module 25: Change Text To Lower Case
Module 25: Change Text To Lower Case
Module 26: Change Text To Upper Case
Module 26: Change Text To Upper Case
Module 27: Change Text To Proper Case
Module 27: Change Text To Proper Case
Module 28: Insert Special Character
Module 29: Use Autofill : A Time Saving Trick
Module PDF's
Module 2 : Data Validation With Date
Module 3 : Data Validation with Whole Numbers
Module 4 : Data Validation with List Feature
Module 12 : AVERAGEIFS Function
Module 13 : CELL Function With 10 Variations
Module 20 : RANDBETWEEN Function (Insert Random Numbers)
Module 22 : Remove Duplicate Values
Module 23 : ROUND Function Up/Down
Module 24 : Add Custom Background Image
Module 25: Change Text To Lower Case
Module 26: Change Text To Upper Case
Module 27: Change Text To Proper Case
Module 28: Insert Special Character