Description
The Excel 2016 Master Bundle is here to help you master Excel and teach you all the top skills and features that you need to know to use the software.
You will be able to use these skills either at the office, or at home and will learn transferable and valuable skills that will be useful for years to come.
What is Microsoft Excel?
Microsoft Excel is a software program created by Microsoft, that uses spreadsheets to organise numbers and data, with formulas and tools such as pivot tables, graphing tools, and much more.
What’s new in Microsoft Excel 2016?
There are differences between Excel 2013 and Excel 2016 – The main changes are associated with the Business Intelligence (BI Features). This can be located on the Insert and Data tabs, including a Forecast Sheet Feature, a Power Maps tool, and several new chart types. As well as this there is a new Get & Transform section, for working with and creating queries.
Who is this course bundle designed for?
The Excel 2016 Master Bundle is designed for anybody no matter your experience. If you want to gain an in-depth thorough understanding of Microsoft Excel, either for business or office then this course is for you.
$564.99 $29.99Add to basket
Courses included in this bundle:
Microsoft Excel for PC – Pivot Tables
Excel 2016 – PC
Excel 2016 for PC – Charting
Excel 2016 for PC – Evaluating Data
Excel 2016 for PC – Frequently Used Formulas & Functions
Excel 2016 for PC – Protecting Your Spreadsheets
Excel 2016 for PC – Statistical Analysis
What’s Included?
Unlimited access for 12 months
Access anywhere, any time
Fast effective training, written and designed by industry experts
Track your progress with our Learning Management System
Unlimited support
Save money, time and travel costs
Learn at your own pace and leisure
Easier to retain knowledge and revise topics than traditional methods
Exam preparation quizzes, tests and mock exams to ensure that you are 100% ready
$564.99 $29.99Add to basket
Modules
Microsoft Excel for PC – Pivot Tables
Module 1 – Getting Started
What you’ll learn in this training
What is a Pivot Table?
Selecting appropriate data for a Pivot Table
Module 2 – Creating & Deleting
Creating a Pivot Table
Selecting various data sources
Quick Analysis Tool
Recommended Pivot Table feature
Classic Pivot Table layout in the grid
Select different source data
Delete a Pivot Table
Module 3 – Custom Appearance & Layout
Add and remove field from a pivot table
Add and use the page field
Change the data area number format
Change how empty cells are displayed
Format a Pivot Table
Sort a Pivot Table
View data detail for an item
View detail for a data cell
Exclude row or column items from the data summary
Group Pivot Table by dates
Module 4 – Printing Options & Copying a Pivot Table
Printing a Pivot Table with column and row field
Insert page breaks after each row item
Copy a Pivot Table
Module 5 – Summarizing with Totals & Formulas
Display grand totals
Change the data summary function
Create a calculated field
Insert a calculated item
List the formulas used in a Pivot Table
Module 6 – Refreshing Data
Refreshing Pivot Table data
Refreshing data at specific time intervals
Module 7 – Pivot Charts
Create a Pivot Chart from an existing table
Create a Pivot Chart from scratch
Change the layout of a Pivot Chart
Convert a Pivot chart to a static chart
Module 8 – Sparklines & Slicers
Use sparklines and slicers
Create sparklines
Change the sparkline type
Format sparklines
View the parts of a slicer
Create a slicer
Format a slicer
Excel 2016 – PC
Module 1 – Getting StartedWhat you’ll learn in this training
Excel spreadsheet structure overview
Creating and saving an Excel file
Interface overviewModule 2 – Work with Data and DatasetsEnter data into a cell
Create a dataset
Auto-fill data
Create simple calculations
Referencing cells
Auto-fill cell references
Format cell data
Cut and paste dataModule 3 – Create FormulasCreate multiple calculation formulas
Use AutoSum
Create an averaging formula
Retrieve the min and max value
Retrieve the number count
Create a correlation
Access and search for built in formulasModule 4 – Manage Data and Datasets
Trace precedents
Trace dependents
Sort data
Changing text to columns
Setup data validation
Delete and insert columns and rows
Module 5 – Format Your Table
Merge cells
Label data
Change cell background color
Change table format
Freeze panes
Create conditional formatting
Insert images and objects
Module 6 – Work with Sheets
Name and add sheets
Reference cells in other sheets
Module 7 – Work with Charts
Insert a chart
Edit a chart
Change the chart type
Change chart data
Module 8 – Share and Secure Data
Protect a sheet
Hide and unhide columns and rows
Add comments to a cell
Save to OneDrive®
Invite people to share
Module 9 – Import and Export Data
Import data from text
Import data from Access®
Import data from XML file
Export Excel as CSV
Export spreadsheets to PDF
Export data as tab delimited text
Printing spreadsheets
Excel 2016 for PC – Charting
Module 1 – Getting StartedWhat you’ll learn
Overview of Charts
Identify elements of a chart
Select chart data in adjacent cells
Select chart data in nonadjacent cells
Charting using the chart group buttons
Quick analysis toolModule 2 – Chart Tabs and RibbonsView a sample chart
Create column and bar charts
Create line charts
Create pie and doughnut charts
Create scatter and bubble charts
Create area charts
Create radar charts
Create surface charts
Create stock charts
Create a chart template
Create a combo chart
Create a trendline for a chartModule 3 – Format Your ChartAdd titles and labels
Change the chart type
Format the chart area
Add a border and/or patterns to the plot area
Format the axis properties
Set format options for titles and labels
Set the placement and font for the legend
Change the data series
Change the data series pattern or marker
Change the shape of a 3D data marker
Change the plot order
Resize and reposition
Show or hide the data table
Change the gap and overlap values
Change the elevation and rotation
Pulling out slices
Use a graphic or pictureModule 4 – Present Your Chart
Place a chart on a separate chart sheet
Print a chart sheet
Print embedded charts
Insert a static copy of a chart
Insert a linked copy of a chart
Save your chart as a Web page
Module 5 – Work with Sparklines
Create sparklines
Group and ungroup sparklines
Edit the sparkline data
Change the sparkline type
Show sparkline data points
Format the sparkline
Change axis settings
Change hiddine and empty cell settings
Clear sparklines
Module 6 – New Chart Types
Creating a histogram chart and pareto lines
Creating a waterfall chart
Creating box and whisker plots
Creating tree maps and sunburst maps
Excel 2016 for PC – Evaluating Data
Module 1 – Getting StartedWhat you’ll learn
Module 2 – Data Validation
Specify data validation criteria
Create data validation rule messages
Identify invalid data within a data validation range
Create a data validation drop-down list
Find cells that contain Data Validation rules
Clear existing Data Validation rules
Module 3 – Conditional Formatting
Understand Conditional Formatting
Apply Greater Than Conditional Formatting
Apply other conditional operators
Checking for duplicate values
Apply Top/Bottom rules
Apply data bars
Apply color scales
Apply icon sets
Create a Conditional Formatting rule
Manage Conditional Formatting rules
Clear Conditional Formatting rules
Excel 2016 for PC – Frequently Used Formulas & Functions
Module 1 – Getting Started & What’s NewWhat you’ll learn
TEXTJOIN
IFS
MAXIFS and MINIFSModule 2 – FormulasSUM function
AVERAGE function
COUNT function
DATE and NOW functions
DAYS function
MATCH function
TRANSPOSE function
ROUND function
AND function
OR function
IF function
NESTED function
COUNTIF function
SUMIF function
VLOOKUP function
HLOOKUP function
CONCATENATE (CONCAT) function
Text to columnsModule 3 – Linking and Consolidating WorksheetsCreate a link formula within the same workbook
Create a link formula to another workbook
Consolidate values from multiple workbooks
Consolidate worksheet values within a function
Absolute links vs. relative links
Consolidate command with multiple sheets
Consolidate command with multiple workbooks
Open a workbook with external reference formulas
Break links to external referencesModule 4 – Auditing & Error Checking
Display formulas
Identify precedent cells
Identify dependent cells
Check for errors
Evaluate a formula
Excel 2016 for PC – Protecting Your Spreadsheets
Module 1 – Protecting Your SpreadsheetProtecting a worksheet
Unprotecting a workbook
Using a password to protect a worksheet
Unprotecting a worksheet
Locking and unlocking specific cells
Protecting the structure of a workbook
Marking workbook as final
Excel 2016 for PC – Statistical Analysis
Module 1 – Describing DataWhat you’ll learn in this training
Finding the arithmetic mean
AVERAGEA function
AVERAGEIF function
AVERAGEIFS function
Find the geometric mean
Find the median
Find the mode
Find the standard deviation
Use the STDEVA functions
Find the variance
Use the VARA functions
Find the kurtosis of a distribution
Find the skewness of a distribution
Find the confidence interval
Use the FREQUENCY function
Find the quartile of a data set
Ranking values
Finding a value’s standard scoreModule 2 – Comparing VariablesFind the bivariate correlation coefficient
Find the values associated with a linear regression
Find linear slope
Find the y-intercept of a linear regression
Predict a future value using an existing value
TREND function
Find the values associated with an exponential regression
Predicting values along an exponential curve using existing valuesModule 3 – Tests of SignificanceFinding the probability of a one-tailed Z-Test
Finding the probability associated with a Student’s T-Test for paired samples
Finding the critical value of the Student’s T-Test for paired samples
Examining data for homogeneity of variance using F-TEST function
Finding the critical value of the F-test
Finding the probability associated with a student’s T-Test for independent samples
Finding the critical value of the student’s T-Test for independent samples
Finding the probability associated with a chi-squared test for independence
Finding the critical value of the chi-squared test for independenceModule 4 – Printing Options & Copying a Pivot Table
Printing a Pivot Table with column and row field
Insert page breaks after each row item
Copy a Pivot Table
Module 5 – Data Analysis ToolPak
Activating the data analysis ToolPak
Finding descriptive statistics
Create a histogram
Performing a T-test for paired or related samples
Examining data for homogeneity of variance
Performing a T-test for independent or unrelated samples
Performing a one-way analysis of variance (ANOVA)
Performing a factorial analysis of variance (ANOVA)
Performing a repeated measures analysis of variance (ANOVA)
Finding the correlation of two or more variables
Performing a linear regression
System Requirements
Minimum specifications for the computer are:
Windows:
Microsoft Windows XP, or later
Modern and up to date Browser (Internet Explorer 8 or later, Firefox, Chrome, Safari)
MAC/iOS:
OSX/iOS 6 or later
Modern and up to date Browser (Firefox, Chrome, Safari)
All systems:
Internet bandwidth of 1Mb or faster
Flash player or a browser with HTML5 video capabilities (We recommend Google Chrome)