Let us help you find the training program you are looking for.

If you can't find what you are looking for, contact us, we'll help you find it. We have over 800 training programs to choose from.

banner-img

Course Skill Level:

Foundational to Intermediate

Course Duration:

3 day/s

  • Course Delivery Format:

    Live, instructor-led.

  • Course Category:

    Big Data & Data Science

  • Course Code:

    DABME3L21E09

Who should attend & recommended skills:

Those with Excel with basic Excel and IT experience seeking to manage, analyze, and visualize data

Who should attend & recommended skills

  • This course is geared for those who want learn various ways to model data for businesses and to manage, analyze, and visualize data with Microsoft Excel 2013 to transform raw data into ready to use information.
  • Skill-level: Foundation-level Excel Data Analysis skills for Intermediate skilled team members. This is not a basic class.
  • IT skills: Basic to Intermediate (1-5 years’ experience)
  • Excel: Basic to Intermediate (1-5 years’ experience)

About this course

Data Analysis and Business Modeling with Excel 2013 is one of the easiest to use data analysis tools you will ever come across. Its simplicity and powerful features has made it the go to tool for all your data needs. Complex operations with Excel, such as creating charts and graphs, visualization, and analyzing data make it a great tool for managers, data scientists, financial data analysts, and those who work closely with data. Learning data analysis and will help you bring your data skills to the next level.
This course starts by walking you through creating your own data and bringing data into Excel from various sources. You’ll learn the basics of SQL syntax and how to connect it to a Microsoft SQL Server Database using Excel’s data connection tools. You will discover how to spot bad data and strategies to clean that data to make it useful to you. Next, you’ll learn to create custom columns, identify key metrics, and make decisions based on business rules. You’ll create macros using VBA and use Excel 2013’s shiny new macros. Finally, at the end of the course, you’ll be provided with useful shortcuts and tips, enabling you to do efficient data analysis and business modeling with Excel 2013.

Skills acquired & topics covered

  • Working in a hands-on learning environment, led by our Excel Data Analysis expert instructor, students will learn about and explore:
  • Creating formulas to help you analyze and explain findings
  • Developing interactive spreadsheets that will impress your audience and give them the ability to slice and dice data
  • A step-by-step guide to learn various ways to model data for businesses with the help of Excel 2013
  • Discovering what Excel formulas are all about and how to use them in your spreadsheet development
  • Identifying bad data and learn cleaning strategies
  • Creating interactive spreadsheets that engage and appeal to your audience
  • Leveraging Excels powerful built-in tools to get the median, maximum, and minimum values of your data
  • Building impressive tables and combine datasets using Excels built-in functionality
  • Learning the powerful scripting language VBA, allowing you to implement your own custom solutions with ease

Course breakdown / modules

  • Getting started with data
  • Manually creating data
  • Importing data from various sources

  • Reading a table from MSSQL – the Microsoft SQL Server database
  • Reading multiple tables from MSSQL
  • Reading from MSSQL using SQL

  • Leading/trailing/in-between spaces
  • Capitalization
  • Duplicates
  • Text to Columns
  • Combine data from multiple columns into one column
  • Fixing similar words
  • Text to dates
  • Text to numbers

  • How to create formulas
  • Combining strings and numbers
  • Using built-in functions
  • If/else/then statements
  • Comparing columns

  • Gathering data
  • Preparing the data for analysis
  • Analyzing our data

  • Gathering data
  • Preparing data
  • Analyzing data
  • Data models

  • Activating the Excel Solver
  • Modeling our linear programming problem
  • Using the Excel Solver

  • What is VBA?
  • What is a macro?
  • Opening the VBA Editor
  • Your very first "Hello World" VBA script

  • Quick analysis charts
  • Charting options
  • Additional design options

  • What are slicers?
  • Final tweaks