Microsoft Excel
To reduce carbon footprint, and reduce cost to you, we are a “paperless” course. Your course materials are available in digital format, and may be printed on-demand if a paper copy is required. This portal will serve as your one stop shop for all the information you need for your course. In this class, we will be using MS Excel. Depending on the version licensed to your computer, it may be a standalone application; or, it may be bundled under Microsoft Office, or Office 365.
What is Microsoft Excel? Excel is a spreadsheet program from Microsoft and a component of its Office product group for business applications. Microsoft Excel enables users to format, organize and calculate data in a spreadsheet. This class is focused on beginner through intermediate skills necessary to use excell in a corporate setting.
Beginner level Excel user skills include navigation & formatting of the worksheets, sorting, filtering, and basic calculation.
Intermediate level Excel user skills include options and methods to manage and work data in an efficient way, using spreadsheets, graphing, tables, formulas.
Advanced level skills in Excel means possessing the ability to use automation efficiently to process large quantities of data relevant to business tasks.
What is Microsoft Office? The term “Microsoft Office” refers to Microsoft’s entire suite of office productivity applications. Microsoft Word is one of the many applications that are grouped under the “Microsoft Office” umbrella.
What is Office 365? Office 365 is a service where you pay a monthly subscription fee (around $10 a month) to use Microsoft Office programs (as opposed to paying $100 or more up front, as was traditionally done). One benefit to using Office 365 is that software updates are free (for example, if a new version of Microsoft Word comes out, you can upgrade to that new version for free).
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.
Materials
MS Excel Manual (2010)
Beginner level recap navigation & formatting of the worksheets, sorting, filtering, and basic calculation.
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