Excel Data Analysis For Dummies book cover

Excel Data Analysis For Dummies

Author:
Paul McFedries
Published: February 15, 2022

Overview

Turn jumbles of numbers into graphics, insights, and answers with Excel With Microsoft Excel, you can, well, excel at data analysis. And Excel Data Analysis For Dummies can help, with clear and easy explanations of the mountain of features for creating, visualizing, and analyzing data. PivotTables, charts, what-if analysis, statistical functions—it's all in here, with examples and ideas for Excel users of all skill levels. This latest edition covers the most recent updates to Excel and Microsoft 365. You'll beef up your data skills and learn powerful techniques for turning numbers into knowledge. For students, researchers, and business professionals, Excel is the spreadsheet and data application of choice—and Dummies is the best choice for learning how to make those numbers sing.
  • Learn how to use Excel's built-in data analysis features and write your own functions to explore patterns in your data
  • Create striking charts and visualizations, and discover multiple ways to tell the stories hidden in the numbers
  • Clean up large datasets and identify statistical operations that will answer your questions
  • Perform financial calculations, database operations, and more—without leaving Excel
Excel Data Analysis For Dummies is the go-to resource for Excel users who are looking for better ways to crunch the numbers.
Turn jumbles of numbers into graphics, insights, and answers with Excel With Microsoft Excel, you can, well, excel at data analysis. And Excel Data Analysis For Dummies can help, with clear and easy explanations of the mountain of features for creating, visualizing, and analyzing data. PivotTables, charts, what-if analysis, statistical functions—it's all in here, with examples and ideas for Excel users of all skill levels. This latest edition covers the most recent updates to Excel and Microsoft 365. You'll beef up your data skills and learn powerful techniques for turning numbers into knowledge. For students, researchers, and business professionals, Excel is the spreadsheet and
data application of choice—and Dummies is the best choice for learning how to make those numbers sing.
  • Learn how to use Excel's built-in data analysis features and write your own functions to explore patterns in your data
  • Create striking charts and visualizations, and discover multiple ways to tell the stories hidden in the numbers
  • Clean up large datasets and identify statistical operations that will answer your questions
  • Perform financial calculations, database operations, and more—without leaving Excel
Excel Data Analysis For Dummies is the go-to resource for Excel users who are looking for better ways to crunch the numbers.
Excel Data Analysis For Dummies Cheat Sheet

Data analysis, by definition, requires some data to analyze. However, after you’ve imported or entered that data and cleaned it up as best you can. what’s your next move? Ah, that’s where the “analysis” part raises its hand in the air and says, “Pick me, pick me!” Excel is bursting at its digital seams with tools for analyzing data, but there are some that you’ll turn to most often. Here you’ll find quick guides for Excel’s descriptive statistics tools, building comparison expressions, and wielding the powerful database functions.

Articles From The Book

13 results

Excel Articles

How to Use Excel’s Descriptive Statistics Tool

Perhaps the most common data analysis tool that you'll use in Excel is the one for calculating descriptive statistics. To see how this works, take a look at this worksheet. It summarizes sales data for a book publisher. In column A, the worksheet shows the suggested retail price (SRP). In column B, the worksheet shows the units sold of each book through one popular bookselling outlet. You might choose to use the Descriptive Statistics tool to summarize this data set. To calculate descriptive statistics for the data set, follow these steps:

  1. Click the Data tab’s Data Analysis command button to tell Excel that you want to calculate descriptive statistics.

    Excel displays the Data Analysis dialog box.

  2. In the Data Analysis dialog box, highlight the Descriptive Statistics entry in the Analysis Tools list and then click OK.

    Excel displays the Descriptive Statistics dialog box.

  3. In the Input section of the Descriptive Statistics dialog box, identify the data that you want to describe.

    • To identify the data that you want to describe statistically: Click the Input Range text box and then enter the worksheet range reference for the data. In the case of the example worksheet, the input range is $A$1:$C$38. Note that Excel wants the range address to use absolute references — hence, the dollar signs.

      To make it easier to see or select the worksheet range, click the worksheet button at the right end of the Input Range text box. When Excel hides the Descriptive Statistics dialog box, select the range that you want by dragging the mouse. Then click the worksheet button again to redisplay the Descriptive Statistics dialog box.

    • To identify whether the data is arranged in columns or rows: Select either the Columns or the Rows radio button.

    • To indicate whether the first row holds labels that describe the data: Select the Labels in First Row check box. In the case of the example worksheet, the data is arranged in columns, and the first row does hold labels, so you select the Columns radio button and the Labels in First Row check box.

  4. In the Output Options area of the Descriptive Statistics dialog box, describe where and how Excel should produce the statistics.

    • To indicate where the descriptive statistics that Excel calculates should be placed: Choose from the three radio buttons here — Output Range, New Worksheet Ply, and New Workbook. Typically, you place the statistics onto a new worksheet in the existing workbook. To do this, simply select the New Worksheet Ply radio button.

    • To identify what statistical measures you want calculated: Use the Output Options check boxes. Select the Summary Statistics check box to tell Excel to calculate statistical measures such as mean, mode, and standard deviation. Select the Confidence Level for Mean check box to specify that you want a confidence level calculated for the sample mean.

      Note: If you calculate a confidence level for the sample mean, you need to enter the confidence level percentage into the text box provided. Use the Kth Largest and Kth Smallest check boxes to indicate you want to find the largest or smallest value in the data set.

      After you describe where the data is and how the statistics should be calculated, click OK. Here are the statistics that Excel calculates.

Here is a new worksheet with the descriptive statistics calculated.

Excel Articles

How to Calculate Moving Averages in Excel

The Data Analysis command provides a tool for calculating moving and exponentially smoothed averages in Excel. Suppose, for sake of illustration, that you’ve collected daily temperature information. You want to calculate the three-day moving average — the average of the last three days — as part of some simple weather forecasting. To calculate moving averages for this data set, take the following steps.

  1. To calculate a moving average, first click the Data tab’s Data Analysis command button.

  2. When Excel displays the Data Analysis dialog box, select the Moving Average item from the list and then click OK.

    Excel displays the Moving Average dialog box.

  3. Identify the data that you want to use to calculate the moving average.

    Click in the Input Range text box of the Moving Average dialog box. Then identify the input range, either by typing a worksheet range address or by using the mouse to select the worksheet range.

    Your range reference should use absolute cell addresses. An absolute cell address precedes the column letter and row number with $ signs, as in $A$1:$A$10.

    If the first cell in your input range includes a text label to identify or describe your data, select the Labels in First Row check box.

  4. In the Interval text box, tell Excel how many values to include in the moving average calculation.

    You can calculate a moving average using any number of values. By default, Excel uses the most recent three values to calculate the moving average. To specify that some other number of values be used to calculate the moving average, enter that value into the Interval text box.

  5. Tell Excel where to place the moving average data.

    Use the Output Range text box to identify the worksheet range into which you want to place the moving average data. In the worksheet example, the moving average data has been placed into the worksheet range B2:B10.

  6. (Optional) Specify whether you want a chart.

    If you want a chart that plots the moving average information, select the Chart Output check box.

  7. (Optional) Indicate whether you want standard error information calculated.

    If you want to calculate standard errors for the data, select the Standard Errors check box. Excel places standard error values next to the moving average values. (The standard error information goes into C2:C10.)

  8. After you finish specifying what moving average information you want calculated and where you want it placed, click OK.

    Excel calculates moving average information.

Note: If Excel doesn't have enough information to calculate a moving average for a standard error, it places the error message into the cell. You can see several cells that show this error message as a value.

Excel Articles

What are PivotTables in Excel? What Can You Do with Them?

In a general sense, Excel PivotTables take a large amount of information and condense that data into a report that tells you something useful or interesting. For example, take a look at the following table. This table contains well over 100 records, each of which is an order from a sales promotion. That’s not a ton of data in the larger scheme of things, but trying to make sense of even this relatively small data set just by eyeballing the table’s contents is futile. For example, how many earbuds were sold via social media advertising? Who knows? Ah, but now look at the image below, which shows an Excel PivotTable built from the order data. This report tabulates the number of units sold for each product based on each promotion. From here, you can quickly see that 322 earbuds were sold via social media advertising. That is what PivotTables do. PivotTables help you analyze large amounts of data by performing three operations: grouping the data into categories; summarizing the data using calculations; and filtering the data to show just the records you want to work with:

  • Grouping: A PivotTable is a powerful data-analysis tool in part because it automatically groups large amounts of data into smaller, more manageable chunks. For example, suppose you have a data source with a Region field in which each item contains one of four values: East, West, North, and South. The original data may contain thousands of records, but if you build your PivotTable using the Region field, the resulting table has just four rows — one each for the four unique Region values in your data.

You can also create your own grouping after you build your PivotTable. For example, if your data has a Country field, you can build the PivotTable to group all the records that have the same Country value. When you have done that, you can further group the unique Country values into continents: North America, South America, Europe, and so on.

  • Summarizing: In conjunction with grouping data according to the unique values in one or more fields, Excel also displays summary calculations for each group. The default calculation is Sum, which means that for each group, Excel totals all the values in some specified field. For example, if your data has a Region field and a Sales field, a PivotTable can group the unique Region values and display the total of the Sales values for each one. Excel has other summary calculations, including Count, Average, Maximum, Minimum, and Standard Deviation.

Even more powerful, a PivotTable can display summaries for one grouping broken down by another. For example, suppose your sales data also has a Product field. You can set up a PivotTable to show the total Sales for each Product, broken down by Region.

  • Filtering: A PivotTable also enables you to view just a subset of the data. For example, by default, the PivotTable’s groupings show all the unique values in the field. However, you can manipulate each grouping to hide those that you do not want to view. Each PivotTable also comes with a report filter that enables you to apply a filter to the entire PivotTable. For example, suppose your sales data also includes a Customer field. By placing this field in the PivotTable’s report filter, you can filter the PivotTable report to show just the results for a single Customer.

Excel PivotTable features

You can get up to speed with PivotTables very quickly after you learn a few key concepts. You need to understand the features that make up a typical PivotTable, particularly the four areas — row, column, data, and filter — to which you add fields from your data. Check out the following PivotTable features:
  • Row area: Displays vertically the unique values from a field in your data.
  • Column area: Displays horizontally the unique values from a field in your data.
  • Value area: Displays the results of the calculation that Excel applied to a numeric field in your data.
  • Row field header: Identifies the field contained in the row area. You also use the row field header to filter the field values that appear in the row area.
  • Column field header: Identifies the field contained in the column area. You also use the column field header to filter the field values that appear in the column area.
  • Value field header: Specifies both the calculation (such as Sum) and the field (such as Quantity) used in the value area.
  • Filter area: Displays a drop-down list that contains the unique values from a field. When you select a value from the list, Excel filters the PivotTable results to include only the records that match the selected value.

Find out how to create a PivotTable in Excel 2019.