The course consists of 14 modules which teach and demonstrate the basics tools and techniques of Excel. At the end of the course, students will be able to use Excel, label worksheets, do arithmetic calculations, perform logical analytical tests, and use specific functions.

These are summarized as follows:


1. Introduction 

What is the User Interface / Parts of Excel?
Opening Excel, User interface, uses of Excel, parts of Workbook, Worksheet, Tool Bar, Formula Bar, Columns, Rows, Tabs, Name Box.


2. How to Add and Edit Data for beginners

Entering data into a worksheet, Title, Widen columns, add Rows and Columns, Wrap Text, Edit Text (bold, italics, underline), add Color, Gridlines, Alignment (Left, Center, Right), use Number Format (decimal, integer, currency).


3. Math Calculation

Use the Math Operators for addition (+), subtraction (-), multiplication (*) and division (/); copying Formulas. All formulas start with an equal (=) sign.

4. The VLOOKUP Formula

To extract specific range of data from a large database.

Format: =VLOOKUP (lookup_value, table, array, column index number, [range lookup])

Only exact match with query will be copied; for duplicate entries, only the first occurrence will be matched.


5. IF & AND formula

Logical operators IF and AND provide conditional formulae for comparing and extracting data.
IF is used when comparing one logical condition while AND compares more than one logical conditions.
Format:

=IF(logical_test, [value_if_true], [value_if_false])

=IF(AND (condition 1, condition 2) “yes”, “no”)


6. How to use CHART

Chart allows for a visual presentation of numbers and tables of raw data or analysis of data.
Three ways to make a basic chart are:

1. Select data, press Alt+F1, Design.
2. Quick Analysis, Chart, Hover over option.
3. Insert – chart to view recommended charts; hover see options; F11 to save to separate worksheet.

7. How to create Pivot Table
Possibly one of the most powerful tools in Excel. Used to work on a small part of a large database by extracting data of interest.

Pivot Table Screen – Info Box, Filters, Columns, Rows, Values.

Format: Select Insert >PivotTable >PivotTable > data range > Output range.


8. How to use Flash Fill
To make your editing quick and easy by automatically filling in your data when it senses a pattern.

Turn on Flash Fill to make editing data quick and easy. Flash Fill will learn from the first occurrence and copy to subsequent cells.

Click File, Options, Advanced Options, Check Automatic Flash Fill.


9. How to use the CONCATENATE Function with IF and COUNTA

Concatenate is to join two or more values together.
Format:

Result Cell =CONCATENATE(cell1, cell2)

Include space =CONCATENATE(cell1, “ “, cell2)


10. How to use the Max and Min Function

These are relatively easy functions to use i.e. to select the highest or lowest value in a list of data.
Format:

Result cell =Max(cell1, cell2)

Result cell =Min(cell1, cell2)

Alternate method:

Click Formula / Insert/ Max/ Go 1^st cell, last cell, enter.


11. How to use the Transpose Function

Two ways to transpose data (i) long way (ii) transpose function

• Copy & Paste special method: click, copy, copy special, click target cell, click paste, select paste transpose.
• Use the Transpose function format.

=TRANSPOSE(select table), CTRL + SHIFT + ENTER


12. How to use DateDif Function – to determine the period between two dates

Use TODAY function to automatically fill in today’s date; Format =TODAY(), enter

DATEIF function format: =DATEIF(final cell, today( )” “Y”) where Y = Years


13. How to use the SumIf and SumIfs Functions

Use SUMIF to determine Sum of values in a range based on one criterion.

Use SUMIFS to determine the sum if based on more than one criteria.

Format:          = SUMIF(start cell: end cell, “target”, range)

Format:          = SUMIFS(range1, range2, “target”, range, “subtarget’)


14. How to create an Input and Output Table
To make the calculation easier to track key activities, etc. using the SUMIF formula.
Format:

= SUMIF(start cell, input information, input quantity, input(quantity))


15. BONUS: Excel 2016 Tips and Tricks