Beginner level Excel skills include navigation & formatting of the worksheets, sorting, filtering, and basic calculation.
Intermediate level Excel skills include options and methods to manage and work data in an efficient way.
Advanced level knowledge of Excel means possessing the ability to use spreadsheets, graphing, tables, calculations, and automation efficiently to process large quantities of data relevant to business tasks.
1 Beginner - Basic Workbook Skills
o Moving around a worksheet
o Navigating the workbook
o Creating a workbook
o Selecting cells and ranges
o Opening, closing, and saving workbooks
o Entering data into cells EX 9
o Entering values into ranges
o Entering multiple lines EX 11
o Entering Dates, numbers, %, etc EX 12
2 Creating Formulas EX 30
o Writing formulas in cells
o Precedence EX 31
o Using the Formula Bar
o AutoSum and other basic functions EX 34
o Automatic calculation EX 36
3 Working with Functions EX 127
o Inserting Functions EX 131
o Using the Paste Function tool
o Absolute and relative cell references EX 118-121
o Using range names in functions
o Creating nested functions
o Logical functions (SUMIF, COUNTIF) EX 151
o The IF function and nested IFs
o Using VLOOKUP
4 Working with Data
o Sorting Data
o Custom sorts
o The Advanced Filter tool
o Inserting Charts
o Using the Chart Recommendation Feature
o Editing Charts
o Using Chart Tools
o Using the Quick Analysis Tool
o Add and Format Objects
5 Collaboration Tools
o Protecting a workbook
o Protecting worksheets and cells
o Adding comments to cells
o Sharing workbooks
o Tracking changes
Materials
MS Excel Manual (2010)
Functions to know:
SUM: With this function, you can sum values from multiple cells, or you can also input values directly into the function.
SUMIF: With this function, you can add a criteria to sum values from multiple cells.
COUNT: This function returns the count of numeric values in a cell. You can refer to the cells where you have values or simply insert the values into it.
COUNTIF: This function allows you to add a criteria to returns the count of numeric values in a cell.
AVERAGE: It returns the average of numeric values. You can refer to the cells where you have the values or simply insert them into the function.
DAYS: Returns number of days between two dates
TIME: It returns a valid time serial number as per Excel’s time format. You need to specify hours, minutes, and seconds.
TODAY: Returns todays date each time the Workbook is opened.
NOW: It returns the current date and time (using your system’s settings) in the cell where you insert it.
LEFT: It extracts specific characters from a cell/string starting from the left (start). You need to specify the text and number of characters to extract.
RIGHT: It extracts specific characters from a string starting from the right (last). You need to specify the text and number of characters to extract.
VLOOKUP: It looks up for a value in a column and can return that value or a value from the corresponding columns using the same row number.
IF: The IF function (tests a condition) returns a value when the specific condition is TRUE and returns another value if that condition is FALSE.