Microsoft Excel: Introduction – Intermediate – Advanced
INTRODUCTION
Course Description: This basic Microsoft Excel course is ideal for beginners who want to learn how to produce spreadsheets, work with data and perform basic calculations
Performance Based Objectives
• Upon successful completion of this course, participants will be able to:
• Create, modify and format a spreadsheet
• Use basic formulas and functions
• Calculate and manage multiple worksheets
• Create a chart
• Customise the print settings and print
Course prerequisites: Participants will need to be familiar with Microsoft Windows and basic Windows techniques such as opening, closing and saving files.
Course Outline
| Overview • Navigation shortcuts • Quick range selection techniques • Getting data into your spreadsheet • Data entry ranges Formatting the worksheet • Adjust column widths • Indenting • Centre aligning • Merging and centring • Text Wrap• Formulas • Navigation shortcuts • Quick range selection techniques • Getting data into your spreadsheet • Data entry ranges |
Multiple worksheets • Copy and paste • Copy an entire sheet • Calculating across worksheets • Copying formulas • Renaming sheets • Inserting/deleting a sheet • Grouping sheets• Functions • The AVERAGE function • The MAX Function • The MIN Function Formatting text and numbers • Font formatting • Number formatting • Borders • Cell shading• Format painter • Adding cell comments |
Working with columns and rows • Inserting columns/rows • Deleting columns/rows • Hiding columns/rows • Freeze panes • AutoFill sequences Sorting and filtering • Quick list sorting • Basic list filtering• Charts • Selecting data to chart • Creating a chart • Changing the chart type • Switching columns/rows • Adding titles • Reposition the legend • Using chart layouts • Printing a chart • Pie chart specifics |
Printing and page setup • Print preview • Margins, orientation, scaling • Page layout view • Page break preview • Headers and footers • Printing titles on all pages • Setting a print area • Save as PDF Working with OneDrive Files • Saving to OneDrive • Opening a file in Excel Online |
Book your place today: Daria Choromanska – daria@cita.ie – 086 014 4853
INTERMEDIATE
Course Description: This course has been developed for people wanting to utilise Excel to perform calculations using a variety of common worksheet functions, filter, sort and summarise database lists, format and modify charts, and conditionally format cells.
Performance Based Objectives
• Understand and use a range of common worksheet functions
• Understand and utilise Absolute Referencing
• Organise a database list by advanced filtering and subtotalling
• Utilise Conditional Formatting to change cell appearance
Course prerequisites: Participants will need to have attended or be familiar with the topics covered in the Microsoft Excel Introduction course.
Course Outline
| Functions and Formulas • Absolute cell referencing • Function syntax • Common statistical functions • The COUNTIF function • The SUMIF function • The IF Function• Working with date and time • Date functions – TODAY and NOW • EDATE and NETWORKDAYS • Work with time values • AutoFill date sequences |
Conditional formatting • Format cell value • Format using a formula • Editing conditional formatting rules • Adding sparklines• Advanced sorting and filtering • Sorting custom lists • Sorting by colour • Complex filtering criteria • Searching for criteria • SUBTOTAL function • Subtotalling a list • Creating a simple PivotTable |
• Advanced charts • Creating a chart sheet • Switch columns/rows • Create a dual axis chart • Changing a series chart type • Moving a chart • 3D charts • 3D chart rotation • Create a simple, static dashboard |
Book your place today: Daria Choromanska – daria@cita.ie – 086 014 4853
ADVANCED
Course Description: This course exposes several advanced features of Excel for What-if analysis, database analysis and how to build calculations using advanced functions as well as how to automate processes.
Performance Based Objectives
• Build complex calculations with advanced functions
• Link, export and consolidate data
• Limit data entry and build complex models with Lookup functions
• Analyse data with simple Tables, PivotTables and PivotCharts.
• Create and modify Macros
Course prerequisites: Participants will need to have attended or be familiar with all the topics covered in the Microsoft Excel Intermediate course.
Course Outline
| Protecting your data • Workbook protection • Protecting cells • Protecting structureFunctions • Rounding and math • AND and OR • Nested IF • IFS function • VLOOKUP and HLOOKUP • Transposing data • Concatenating • INDEX and MATCH • Using help for functionsRange names • Defining range names • Using names in formulas • Navigating with names • Using named constants • Documenting range namesData Validation • Limiting cell values • Creating drop-down cell lists • Validation error messages• What-if analysis tools • Using goal seek |
Getting and transforming data • Get Data • Transform in Query Editor • Clean up data in Query Editor Tables • Create a table • Add a table formula • Add records to a table • Add totals row • Work with an outline – group and hideWorkbook linking • Workbook linking • Dealing with broken links Data consolidation • Consolidation by cell position • Consolidation by headingsExporting data • Saving as a different file type • Exporting to Word • Pasting data with a link • Copying a graph with a linkPivotTable reports • Create a PivotTable • Rearranging data • Applying filters • Adding fields • Modify field settings • Refreshing data • Formatting a PivotTable |
PivotCharts • Creating a PivotChart • Filtering a PivotChartMacros • Recording a Macro • Editing a Macro • Relative/Absolute Recording • Stepping through a Macro • Macro buttons • Customising the toolbar • Writing a line of VBA code • Deleting a MacroCircular references • Circular error messages • Locating circular references • When circular references are necessary
|
Book your place today: Daria Choromanska – daria@cita.ie – 086 014 4853
For training dates and details please see our Course Schedule