Microsoft Excel for Beginners Certification
Module 23: Concatenate
Duration: Approx 20 minutes
23.1 Concatenate
The Concatenate function is one of the text functions that Excel has, which joins two or more text strings into one string.
23.2 Video Instruction
Concatenate in Excel – 3m16s
23.3 Task
Estimated Time: 10 minutes
Download the worksheet that accompanies this video by clicking below. Once downloaded practice Concatenate in Excel using the video above as a reference.
Option #2: Worksheet without formula
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