Microsoft Excel Data Manipulation, Presentation, and Analysis

Paths

Microsoft Excel Data Manipulation, Presentation, and Analysis

Authors: Pratheerth Padman, Ben Howard, Carlos Gutierrez, Amber Israelsen, Eugene Meidinger

Microsoft Excel offers a robust array of data munging and analytics tools. This skill will help you use these tools to their greatest effect as you collect, explore, analyze,... Read more

What You Will Learn

  • Loading data into Excel from external sources
  • Exploring your data with PivotTables
  • Visualizing your data with PivotCharts
  • Formatting Excel objects
  • Creating calculated cell values and formulas
  • Distributing your Excel workbooks

Pre-requisites

  • Data Analytics Literacy
  • Basic spreadsheets

Beginner

Load data into an Excel workbook, and learn how to search and manipulate data in Excel.

Loading Data into Excel

by Pratheerth Padman

Jun 25, 2019 / 48m

48m

Start Course
Description

This is the first course on the Microsoft Excel Data Manipulation, Presentation, and Analysis path and as such this assumes no technical knowledge about excel. First, you'll start by getting introduced to excel itself. Next, you'll discover what worksheets and workbooks are and how to manipulate them by moving and copying them around. Then, you'll move on to the meat of the course – importing data from various sources into excel. Finally, you'll be given a brief introduction to these sources and the data usually found within and then go through the steps of loading the data into excel. When you're finished with the course, you'll have a primary knowledge about Microsoft Excel and its components and how to load data into Excel from various sources.

Table of contents
  1. Course Overview
  2. Creating and Manipulating Workbooks and Worksheets
  3. Importing Data from Different Sources to Excel

Searching and Manipulating Data in Excel

by Pratheerth Padman

Jul 26, 2019 / 1h 12m

1h 12m

Start Course
Description

If you're a business analyst or even a regular spreadsheet user, rather than creating a spreadsheet from scratch, you'll mostly encounter them already filled with plenty of data. It then becomes imperative that you understand how to search and manipulate data within these.

And helping you do that is exactly what this course - the 2nd in the Microsoft Excel series at Pluralsight - is targeted towards.

In the first two modules, we’ll mostly deal with how to work and manipulate an excel workbook/worksheet that already contains the required data. Here we’ll deal with things like data validation and conducting what-if analysis.

The second module deals with navigating an excel worksheet/workbook. Here we’ll learn methods that help us search the required data and navigate to it. We’ll also talk a bit about inserting and removing hyperlinks.

In the last module, we’ll be dealing with how to insert data into excel cells. This will include learning to paste data with special paste options, filling cells with autofill and inserting and deleting cells themselves.

When you're finished with this course, you'll have the skills and the practice required to search and manipulate spreadsheet data with Microsoft Excel.

Table of contents
  1. Course Overview
  2. Working with Data in Excel
  3. Navigating Worksheets and Workbooks
  4. Entering and Manipulating Data in Cells

Intermediate

Format worksheets and cells, and create calculated cells using Excel formulas.

Formatting Excel Worksheets and Cells

by Pratheerth Padman

Sep 6, 2019 / 1h 28m

1h 28m

Start Course
Description

In the 3rd course in the Excel series, we will be handling the look and feel of an excel worksheet/workbook. Here, we’ll be concentrating on how we want the sheets and books to look like and the tools that help us achieve that. This is an important skill to have in order to create professional-looking spreadsheets.

First, we'll be looking at how to format workbooks and worksheets. Next, we'll look at customizing options and views for worksheets and workbooks. Finally, we'll look at formatting cells and ranges in Excel.

When you're finished with this course, you will have the skills and knowledge to create professional, functional spreadsheets in Microsoft Excel.

Table of contents
  1. Course Overview
  2. Formatting Worksheets and Workbooks
  3. Customizing Options and Views for Worksheets and Workbooks
  4. Formatting Cells and Ranges

Calculating Cell Values with Formulas

by Pratheerth Padman

Nov 7, 2019 / 1h 24m

1h 24m

Start Course
Description

The power and capabilities of Microsoft Excel stem primarily from the things it can do with functions and formulas. As such, learning how to implement, debug, and monitor them becomes an invaluable skill.

This course, Calculating Cell Values with Formulas, aims to help you through that maze.

The first three modules deal with the implementation of functions and formulas to deal with things like performing calculations with aggregate functions, performing logical and statistical operations, referencing and serializing dates and times, and looking up data in Excel.

In the fourth and fifth modules, you’ll focus on troubleshooting and auditing these formulas. You'll explore techniques like tracing precedents and dependents, monitoring cells and formulas with Watch Window, etc.

When you're finished with this course, you'll know how to implement and troubleshoot functions and formulas in Excel.

Table of contents
  1. Course Overview
  2. Implementing Operations and Performing Calculations with Functions and Formulas
  3. Referencing and Serializing Date and Time in Excel
  4. Cell References and Data Lookup in Excel
  5. Monitoring, Validating, and Debugging Cells and Formulas in Excel

Advanced

Summarize and visualize your data with tables and charts, and share your workbooks with others.

Summarizing and Organizing Data in Excel

by Ben Howard

Aug 20, 2019 / 1h 4m

1h 4m

Start Course
Description

Microsoft Excel is the world’s foremost desktop application for storing, organizing, and manipulating data. This course teaches you how you can use the features of Excel to quickly organize, summarize, analyze, and visualize data so that you can understand your data and gain meaningful insights into it. First, you will learn how to order and group the data so that related items appear together. Then, you will discover how to apply totals and sub-totals to it. Finally, you will explore how to visualize the data using Conditional Formatting and Sparklines. By the end of the course, you will have the skills and knowledge to understand and visualize your own data in Excel.

Table of contents
  1. Course Overview
  2. Grouping Data and Outlining Your Data
  3. Inserting Totals and Subtotals
  4. Applying Conditional Formatting
  5. Inserting Sparklines
  6. Exploring Other Excel Capabilities

Displaying Tables with Excel

by Ben Howard

Oct 17, 2019 / 1h 35m

1h 35m

Start Course
Description

Learning how to effectively communicate and display data in Excel is challenging. In this course, Displaying Tables with Excel, you will gain the ability to find insightful information regarding your data stored in Excel. First, you will learn what tables are and when to use them. Next, you will discover how to create tables and format them. Finally, you will explore how to manipulate the table data to provide meaningful insights and answer all those difficult data related questions that your co-workers and managers keep asking! When you’re finished with this course, you will have the skills and knowledge to use Excel Tables in order to correctly analyse your Excel data.

Table of contents
  1. Course Overview
  2. Tables: What Are They and Why Use Them?
  3. Create and Manage Tables
  4. Working with Table Data
  5. Exploring Other Excel Capabilities

Charting Data with Excel

by Carlos Gutierrez

Oct 4, 2019 / 1h 39m

1h 39m

Start Course
Description

Data is an increasingly important part of our organizations, and it's critical that you learn how to effectively communicate and visualize this information. In this course, Charting Data with Excel, you will gain the ability to work with several fundamental components of Excel's charting tools. First, you will learn how to create and change charts. Next, you will discover how to work with multiple data series. Finally, you will explore how to enhance your charts with features like trendlines and secondary axes. When you are finished with this course, you will have the skills and knowledge of Excel charts needed to build a dashboard full of interesting visuals for your data.

Table of contents
  1. Course Overview
  2. Choosing, Creating, and Changing Chart Types
  3. Enhancing the Column Chart with Data Series
  4. Extracting Insights from the Line Chart
  5. Exploring Specialized Visuals for Categorical Data
  6. Exploring Specialized Visuals for Continuous Data
  7. Continue Learning Excel

Exploring Data with PivotTables

by Ben Howard

Nov 22, 2019 / 1h 41m

1h 41m

Start Course
Description

Learning how to summarize and analyze large and complex sets of data, and communicating the results, can be a daunting process. In this course, Exploring Data with PivotTables, you will gain the skills required to quickly summarize and present sophisticated sets of data. First, you will learn what PivotTables are and when you should use them. Next, you will discover how to create PivotTables and change their layout, look, and feel. Finally, you will explore how to add new derived values and custom groups of data, and how to restrict the data that is displayed using interactive slicers and filters, helping you answer all those difficult data related questions that your co-workers and managers keep asking! When you’re finished with this course, you will have the skills and knowledge to use Excel PivotTables in order to summarize, analyze, and present your Excel data.

Table of contents
  1. Course Overview
  2. Creating Your First PivotTable
  3. Modifying PivotTable Formatting
  4. Choosing How PivotTable Values Are Presented and Displayed
  5. Creating New PivotTable Values
  6. Adding Grouping, Slicing, and Filtering Interactivity
  7. Exploring Other Excel Capabilities

Visualizing Data with PivotCharts

by Ben Howard

Dec 11, 2019 / 1h 19m

1h 19m

Start Course
Description

A picture is worth a thousand words, and PivotCharts are no exception, providing a neat way of visualizing the data from a corresponding PivotTable. In this course, Visualizing Data with PivotCharts, you will gain the skills to quickly produce charts to a professional standard, which neatly summarize the associated PivotTable, allowing you to visualize the data and spot any trends within it. First, you will learn what PivotCharts are and the different types of charts available for you to use. Next, you will discover how to create PivotCharts, choose the correct chart type, and finesse the look and feel of the chart. Finally, you will explore how to make the chart truly interactive by adding filters, slicers, and drill-up/drill-down capability, helping you answer all those difficult data-related questions that your coworkers and managers just keep on asking! When you are finished this course, you will have the skills and knowledge to use Excel PivotCharts in order to professionally present your data.

Table of contents
  1. Course Overview
  2. Creating Your First PivotChart
  3. Modifying the PivotChart Formatting
  4. Changing How PivotChart Values Are Presented and Displayed
  5. Adding Interactivity with Filters, Slicers, and Drill Down
  6. Exploring Other Excel Capabilities

Formatting Excel Objects

by Amber Israelsen

Jan 10, 2020 / 1h 16m

1h 16m

Start Course
Description

Microsoft Excel is one of the most commonly-used tools in business today. Whether running calculations, creating reports, or keeping things organized, skills in this area will be in high demand for years to come.

In this course, learn how to take your Excel worksheets from average to awesome using advanced features of object formatting. You’ll first learn how to insert text boxes, shapes and pictures. From there, you’ll explore how to format those objects to more effectively illustrate your data and message, while taking accessibility into account. Finally, you’ll see how to work with charts—applying layouts and styles, adjusting size and location, and adding and modifying various chart elements.

When you’re finished with this course, you’ll have an advanced understanding of Excel objects and how to format them for maximum effect.

Table of contents
  1. Course Overview
  2. Introducing Wired Brain Coffee
  3. Text Boxes, Shapes, and Pictures
  4. Charts
  5. Course Summary and Next Steps

Distributing Excel Workbooks

by Eugene Meidinger

Jan 22, 2020 / 1h 30m

1h 30m

Start Course
Description

By default, Excel Workbooks are not ideal for sharing. In this course, Distributing Excel Workbooks, you will learn how to create and share clean and secure workbooks. First, you will learn how to print just what you want. Next, you will discover the huge number of export options. Finally, you will explore how to make your workbooks secure and accessible. When you are finished with this course, you will have the skills and knowledge to make sure your workbooks are readable, understandable, and accessible.

Table of contents
  1. Course Overview
  2. Printing Exactly What You Want
  3. Exporting to Different Formats
  4. Securing Your Workbook
  5. Identifying Hidden Information or Defects
Offer Code *
Email * First name * Last name *
Company
Title
Phone
Country *

* Required field

Opt in for the latest promotions and events. You may unsubscribe at any time. Privacy Policy

By providing my phone number to Pluralsight and toggling this feature on, I agree and acknowledge that Pluralsight may use that number to contact me for marketing purposes, including using autodialed or pre-recorded calls and text messages. I understand that consent is not required as a condition of purchase from Pluralsight.

By activating this benefit, you agree to abide by Pluralsight's terms of use and privacy policy.

I agree, activate benefit