Minerva Pro

       All about real growth

 

Excel

Get the training you need - select the modules covering the functionality that you want to learn about! Mix and match with other applications and contact us about a delivery programme.

On site training lead by Microsoft Certified Trainers includes the provision of training manuals, exercises, case studies and training PC's. Each module is priced at $120 + GST. Bulk purchase advantages apply for 5 or more modules or 5 or more attendees.

The modules below apply for Office 2016. Chat to us about modules for 2010 or 2013!

Foundation Modules

1. Getting Started                                                           2. The Excel Interface           

· What Microsoft Office Excel 2016 is
· What’s new in Excel 2016
· Open and interact with Excel
· Close Excel
· Create new workbooks
· Open and close existing workbooks
· Save workbooks
· Recognize the different Excel file types
· Recognize and work with the active cell
· Select multiple cells
· Explore worksheets and workbooks
· Zoom in and out of a worksheet
· Open and use the Help interface
· Recognize the difference between online and offline Help
· Get help while in a dialog box

· Work with the Quick Access Toolbar
· Add and remove buttons on the Quick Access Toolbar
· Use the File (Backstage) menu
· Home tab
· Insert tab
· Page Layout tab
· Formulas tab
· Data tab
· Review Tab 

        

 



 3. Excel Basics                                                                4. Editing your Workbook  

· Work with columns, rows, cells and ranges

· Create worksheet labels
· Enter and delete data
· Print a worksheet
· Use Autofill, Autosum and Autocomplete
· Flashfill
·
 Work with basic formulae
· Drag and drop cells
· Cut, copy, and paste cells
· Use the Clipboard and Paste Special
· Insert and delete cells, rows, and columns
· Use undo, redo, and repeat
· Use Custom Actions
· Use the error option button
· Use the AutoFill option button
· Use the Paste Option button
· Use AutoCorrect
· Use spell check
· Use Find and Replace
· Document a worksheet with comments

· Change the size of rows or columns
· Adjust cell alignment and rotate text
· Create custom number and date formats
· Use conditional formatting
· Use the Format Painter
· Merge adjacent cells together
· Use AutoFit
· Find and replace formatting
· Add patterns, colours, and borders to a worksheet
· Work with styles and themes
· Create and format charts
· Enhance charts with drawing tools
· Change the chart type
· Change the source data for a chart
· Work with chart axes and data series
· Save a chart style/layout as a template
· Identify absolute and relative cell references


 5. Printing and viewing your workbook 

· Use Normal view, Page Break Preview, Page   Layout view, and Full Screen view
· Manage a single window
· Create, hide, or unhide a window
· Freeze a pane
· Split a worksheet to view multiple areas at once
· Manage multiple windows
· Switch between open workbooks
· Arrange windows
· Compare workbooks side by side
· Reset windows
· Use synchronous scrolling
· Save a workspace
· Print your workbook
· Use Print Preview
· Set printer options


 


 


 


 


 


 Intermediate Modules

1. Advanced file tasks                                                    2. Working with functions and formulas  

· Navigate around your computer using Windows Explorer
· Perform some basic file management tasks in Windows Explorer
· View files in Windows Explorer
· Use the Navigation Pane in Windows Explorer
· Use AutoRecover
· Publish a workbook in PDF or XPS format
· Protect an entire workbook and a single worksheet
· Convert files in XLS format to the new XLSX    format
· Save workbooks in XLS format
· Mark a workbook as final
· Encrypt the contents of a workbook
· Add A Digital Signature to a workbook
· Manage file properties
· Manage file versions
· View a summary of issues that could occur when sharing the file
· Use the Document Inspector, Accessibility Checker, and Compatibility Checker
· Set compatibility options when working with different file formats 


 

· Understand the difference between relative and absolute cell references
· Use basic mathematical operators
· Use formulas with multiple cell references
· Use the formula auditing commands
· Fix formula errors and recognize common errors
· Change error checking options
· Display and print formulas
· Recognize a function
· Search for functions included with Excel
· Add functions to your worksheet
· Use some useful and simple functions
· Use the IF function
· Work with nested functions
· Break up complex or long functions so they are easier to read
· Use functions and AutoFill together
· Recognize range names
· Define and use range names
· Use commands associated with range names
· Select non-adjacent ranges in the same worksheet
· Use AutoCalculate
· Recognize array formulas
· Define basic array formulas
· Use functions with array formulas
· Use the IF function in an array formula

3. Managing Tables                                                       4. Adding the finishing touches  

· Recognize a table and its features
· Create tables from existing ranges
· Modifying tables
· Use items in the Table Tools contextual tab
· Use the total row
· Recognize records and fields
· Add fields and records by inserting columns and rows
· Delete records or fields
· Clean up duplicate records
· Sort data in a table
· Create AutoFilters to perform custom filtering
· Use advanced filters to fine-tune filtered records
· Copy filtered records to a new location
· Filter your data using wildcard characters
· Validate your data against different criteria
· Validate your data using lists and formulas
· Recognize and use database functions
 

                
 

· Check spelling in your worksheet
· Open and browse the Research Pane
· Use the thesaurus to look for synonyms
· Set the display and editing language in Office 2016
· Use themes to make your work consistent
· Customize theme colours, fonts, and effects
· Save and manage custom theme files
· Add a text box to your worksheet
· Use commands in the Drawing Tools tab
· Edit text box contents
· Add and edit WordArt
· Add a picture to your worksheet from your computer
· Search for and add Clip Art
· Insert a screenshot
· Recognize commands in the Picture Tools tab
· Use the mini toolbar for pictures
· Resize images
· Move images around your worksheet
· Crop images
· Rotate and flip images
· Describe functionality in the Format Picture dialog

5. Showing data as a graphic 

· Add SmartArt to your worksheet
· Add text and photos to SmartArt
· Use commands on the SmartArt Tools tabs
· Edit existing SmartArt
· Change the layout of a SmartArt diagram
· Choose the colour and style for a SmartArt diagram
· Reset the SmartArt diagram back to its default settings
· Convert a SmartArt diagram into individual shapes
· Add symbols or special characters to a worksheet
· Insert an equation
· Use the Equation Tools tab to create custom equations
· Draw and edit shapes in your worksheet
· Recognize a Sparkline and its use
· Add Sparklines to your worksheet
· Use the commands on the Sparkline Tools tab
· Edit Sparkline data
· Clear Sparklines from a worksheet
· Show and hide elements of your Sparkline
· Change the Sparkline style
· Change the Sparkline and marker colours
· Set Sparkline axis options
 


 


 


 


 


 


 


 


Advanced Modules

1. Getting the most from your data                         2. Pivoting data  

· Use automatic outlining
· Display and collapse levels
· Manually group data
· Create subtotals
· Describe scenarios
· Create a scenario
· Save multiple scenarios
· Merge scenarios together
· Create a scenario summary report
· Enable data analysis tools
· Identify available analysis and add-in tools
· Use a one or two input data table
· Use Goal Seek
· Generate reports and scenarios with Solver
· Change Solver values and manage constraints
· Choose a solving method
· Use Solver as a goal seek tool
· Insert, edit, format, and use hyperlinks 

 

3. Charting pivoted data   

· Create a PivotChart from scratch or from an existing PivotTable
· Add data to a PivotChart
· Pivot chart data
· Use the PivotChart contextual tabs (Design, Layout, Format, and Analyse)
· Rename fields
· Change the chart type
· Apply a style to a chart
· Manually format chart elements 


· Describe and create PivotTables
· Use the contextual PivotTable Tools tabs
· Add and remove PivotTable information using the Field List
· Change the Field List layout
· Pivot data
· Expand and collapse data
· Filter, sort, group, and refresh data
· Edit a data source
· Modify PivotTable row and column labels and values
· Use the Layout group on the Design tab
· Apply a style to a PivotTable
· Change PivotTable style options
· Manually format a PivotTable
· Use the PivotTable Options dialog
· Create and use a classic PivotTable frame
· Switch to a classic PivotTable frame
· Add data to a classic PivotTable
· Pivot data in a classic PivotTable

 

 

4. Advanced Data Management and Reporting 

· Create a PivotChart based on external data
· Create a slicer for a PivotChart
· Enable PowerPivot and import data
· Integrate data with relationships
· Create a PivotTable with PowerPivot data


5. Advanced Excel Tasks                                              6. Macros, Visual Basic and Excel Programming

· Understand and use logical functions
· Use the IFERROR function with array formulas
· Differentiate between VLOOKUP and HLOOKUP
· Find an extact or approximate match with VLOOKUP
· Use VLOOKUP an array formula
· Understand Autofill lists
· Create, use, modify and delete custom autofill lists
· Link, consolidate and combine workbooks
· Pivot consolidated data useing the PivotTable and PivotChart wizard

 

· Record, edit, and play a macro
· Modify macro security settings
· Use the Visual Basic Editor when working with macros
· Add code and comments to macros
· Use and record relative reference macros
· Assign a keystroke to a macro
· Copy a macro from a workbook or template
· Declare variables
· Iterate over a range
· Prompt for user input
· Use If, Then, and Else statements