Excel Data Analysis

• Knowledge of Windows and MS Excel basics

Audience

• Business analysts
• Data analysts

Excel is a powerful and versatile tool that can be used for various purposes, such as data entry, calculation, formatting, charting, and analysis. Excel has many built-in functions and features that can help users to manipulate, summarize, and present data in meaningful and insightful ways. Excel also supports data analysis tools such as PivotTables, What-If Analysis, and Forecasting, which can enable users to explore and discover patterns, trends, and relationships in their data.

This instructor-led, live training (online or onsite) is aimed at beginner-level to intermediate-level business analysts and data analysts who wish to use Excel to perform data analysis and reporting for their projects and tasks.

By the end of this training, participants will be able to:

• Use Excel formulas and functions to perform calculations and data manipulation.
• Use Excel data analysis tools such as PivotTables, What-If Analysis, and Forecasting to summarize and visualize data.
• Use Excel charts and graphs to create and customize data visualizations.
• Use Excel data validation and conditional formatting to ensure data quality and highlight data insights.
• Use Excel data import and export features to connect to external data sources and share data with others.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Excel basics

• Styles creation and modification

Creating formulas

• Logical functions
• Mathematical functions
• Search Functions

Organizing the data in the spreadsheet

• Create custom sort lists
• Data consolidation
• Creating outlines
• Page break
• Printable version

Create a PivotTable

• Basic information about pivot tables
• Overview of pivot table report fields
• Create a pivot table report from a list of data
• Change the pivot table layout
• Field filtering
• Changing the calculation type of a data field
• Grouping pivot table data items
• Entering an additional calculation into the data area
• Adding a calculated field
• Adding a calculated item
• Retrieving values from a PivotTable report

Creating charts and pivot charts

• Create PivotCharts
• Changing the chart type
• Create custom chart types
• Adding a trend line to a chart
• Adding new values and changing data for the graph
• Data series and their modification in charts
• Highlighting chart elements
• Data visualization on charts - data series manipulation
• Creating a Pivot Chart from information retrieved from an external database
• Changing the layout of a PivotChart
• Changing the type of PivotChart
• PivotChart field filtering
• Grouping elements of a PivotChart box
• Adding a data table to a PivotChart

Data processing in a spreadsheet

• Conditional formatting
• Custom cell formatting - creating a mask
• Custom conditional formatting
• Summarizing data using subtotals
• Creating scenarios
• Data validation
• Create custom cell formats
• Create your own number formats

Processing information from external data sources

• Import from an Excel file
• Import from csv file
• Import from the website
• Import from a folder

Creating interactive dashboards - Case study