Excel For Data Analysis & Reporting

Quick Analysis tool allows you to quickly create graphs, sparklines, PivotTables, PivotCharts, and statistical functions by just clicking on a button.

Conditional formatting allows you to apply a format to a cell or a range of cells based on certain criteria such as highlight duplicate records, highest values and lowest values.

A Sparkline is a tiny chart in a worksheet cell that allows you to quickly visualize the overall trend of a set of values or to indicate maximum and minimum values.

Participants will learn how to:

• Use Data Bar to spot larger and smaller numbers quickly
• Use Color scales to show data variation
• Use Icon sets to present data
• Apply and Edit Conditional Formatting rules
• Use Sparklines to show data trend
• Use the Recommended Chart Tools
• Use Chart Element, Chart Styles and Chart Filter tools
• Add Trendline to show data trend and forecast data
• Use Table to sort, filter and summarize data

Advanced Filter is helpful in finding data that meets two or more complex criteria such as extracting matches and differences between two columns, filtering rows that match items in another list, finding exact matches including uppercase and lowercase characters.

Participant will learn how to:

• Sort data by multiple levels
• Sort data based on custom list
• Use Custom List in AutoFill
• Use Search box criteria to filter data list
• Use Advanced Filter to filter data list
• Use Advanced Filter to copy records to another worksheet
• Use Advanced Filter to copy only selected columns to another worksheet
• Insert automatic subtotals for related data

Data consolidate feature allows you to assemble and summarize data from separate worksheets or workbooks into a master worksheet or other workbooks, so that you can more easily update and aggregate as necessary.

Participants will learn how to:

• Consolidate Data from Multiple Worksheet
• Consolidate Data from Multiple Workbooks
• Use 3-D Reference Formulas to Consolidate Data

Conditional logical functions allow you to sum, average and count cells that meet multiple criteria. Database functions work with an Excel database to sum, average and count based on a given set of conditions.

VLOOKUP function lookup a value in a table by matching on the first column and retrieve the data (result values) in any column to the right. INDEX, MATCH and SUMPRODUCT functions lookup a value in a table by matching value in any column and retrieve the data from any column in the table.

Participants will learn how to:

• Use absolute references and relative references
• Use defined names to create meaningful formulas
• Use Logical functions: IF, AND, OR and IFS
• Use Statistical functions: COUNTIFS, AVERAGEIFS, SUMIFS, MAXIFS and MINIFS
• Use Math & Trig functions: RANDBETWEEN, SUMIFS, SUMPRODUCT
• Use Database functions: DSUM, DCOUNT, DCOUNTA, DMIN, DMAX and DAVERAGE
• Use Lookup functions: VLOOKUP, XLOOKUP, INDEX and MATCH

Power Query is a user-friendly business intelligence tool that that simplifies the process of importing data from different source files and sorting them into an Excel sheet in the most convenient and usable format.

Participants will learn how to:

• Load Table Data into Power Query Editor
• Use Power Query Editor to Change Data Types
• Import data from Text/CSV file using Power Query
• Import data from Microsoft Access database using Power Query
• Combine Multiple Files from a Folder using Power Query

Flash Fill is a time-saving feature which fills in data automatically when a pattern is provided. You can use it to extract data, insert data, format text and concatenate text.

Power Query includes many pre-build transformation functions that can be used to clean up source data for analysis and reporting. These transformations can be as simple as removing a column or filtering rows, or as common as using the first rows as headers, merge columns, split column, and so on.

Participants will learn how to:

• Use Flash Fill to split text, change text cases, combine text, format numbers and dates
• Use TEXT, LEFT, RIGHT, MID, LEN, FIND function to format and split data
• Use Text To Columns Wizard to split text string and format dates
• Use CONCAT, TEXTJOIN function to combine data
• Use Power Query Editor to merge and split columns

Excel table enables you to manage and analyze a group of related data easier. You can create calculated columns, add slicer to Excel table and create relationships between two tables.

Participants will learn how to:

• Format Data as Excel Table
• Remove duplicated records from table
• Create calculated columns in Excel table
• Create new Conditional Formatting Rules
• Sort data by Icon Sets
• Filter data to show the highest or lowest data
• Filter Excel table records using Slicers
• Create Chart from Excel Table

PivotTable is one of the most powerful and widely used features of Microsoft Excel. It allows you to summarize, analyze and visualize data in various ways that can provide deep insights.

PivotChart is a visual representation of PivotTable that helps to summarize, visualize, and analyze the data in a structured and organized format. It is a wonderful way to visualize data in an effective and efficient manner.

Participants will learn how to:

• Create PivotTable and PivotChart from Excel table
• Use different PivotTable Report Layout
• Hide and show Subtotals and Grand Totals
• Create Report Filter Pages from Report Filter
• Using Column Labels and Row Labels filters
• Summarize values using Statistical functions
• Calculate Percent of Totals
• Calculate Running Totals
• Group dates and numbers
• Customize PivotChart using the new Chart Tools
• Add Slicer and Timeline to PivotChart
• Connect multiple PivotTables to Slicer and Timeline

The case studies are based on real-life business scenarios and problems encountered by Excel users in daily jobs.

Notice:
This course will be running and taught based on thePC Windows software and not Mac versions.(All Laptops and materials will be provided)

*Important disclaimer for participants attending the 2016 version:
Participants are encouraged to inquire which version they have at work torealisewhich features may, or may not, be present on their work-based application.

Look at theFILEtab and search forACCOUNT. It will either highlight ‘PRODUCT ACTIVATED’or ‘SUBSCRIPTION’. Product Activated applications are typically a one-time purchase license and will not carry the same updated features as the Subscription versions. Companies may vary in polices of software purchase depending on the suitability of products. Subscription versions tend to be continually updated

As an example, one the features not available in the Product Activated version is IFS and MAXIFS Functions. Impress Training will be using the 365 subscription version for training purposes.