Excel 2016 for Mac brings lots of welcome improvements to the workhorse spreadsheet but also leaves out useful tools.

The Analysis ToolPak. The Analysis ToolPak is an add-in for Microsoft Excel that comes with Microsoft Excel. An add-in is simply a hidden workbook that adds commands or features to Excel.

The Analysis ToolPak is an add-in program that adds advanced statistical functions to Excel, such as variance, correlation, frequency and sampling analysis. Enabling the Data Analysis Toolpak in Excel for Mac. Enabling the Data Analysis Toolpak in Excel 2016 for Mac. The following instructions are based on Microsoft's published support documentation as of July 7, 2015, and apply only to Office 2016 for Mac.

Pros

Moving to the cloud: Like the other apps in the latest Mac Office suite, Excel 2016 lets you store, sync, and edit files online, via Microsoft's OneDrive cloud storage service. You can also save Excel files to your Dropbox account.

Collaborate: Excel 2016, through OneDrive, lets you share documents with colleagues and collaborate in real time. You can chat online as well, either through a document chat window or through Skype.

Modern design: The redesigned ribbon for the Mac version of Excel borrows the look and feel of its Windows counterpart, presenting tools and formatting options in much the same way as Excel for Windows does. Excel also recommends appropriate charts based on spreadsheet data.

Moving across platforms: Excel 2016 supports many of the Windows keyboard shortcuts and most of the Excel 2013 for Windows functions, which should make it easier to use the tool across platforms. The update also includes an Analysis ToolPak add-in, PivotTable slicers for analyzing data, and an improved formula builder.

Cons

Macro limits: Prior to Office 2016, you could build macros in Excel for Mac. The 2016 edition offers what Microsoft calls a 'simplified' Visual Basic Editor (VBE), which allows you to debug existing macros. But if you want to build new macros, you need to do that on the Windows side or use an earlier Mac version. For those who built macros in preceding Mac editions, this is a big loss.

Bottom Line

Excel 2016 is probably worth the upgrade just based on its ability to take advantage of OneDrive. The new analysis tools and formula builder help keep Microsoft's spreadsheet an essential tool. Hobbling its macros tool, however, diminishes a bit of the attraction for serious users.

More Resources

What do you need to know about free software?

The tutorial shows 3 different techniques to plot a histogram in Excel - using the special Histogram tool of Analysis ToolPak, FREQUENCY or COUNTIFS function, and PivotChart.

While everyone knows how easy it is to create a chart in Excel, making a histogram usually raises a bunch of questions. In fact, in the recent versions of Excel 2019, 2016, Excel 2013, and Excel 2010, creating a histogram is a matter of minutes and can be done in a variety of ways - by using the special Histogram tool of the Analysis ToolPak, formulas or the old good PivotTable. Further on in this tutorial, you will find the detailed explanation of each method.

• How to customize and improve a histogram chart

What is a histogram in Excel?

Wikipedia defines a histogram in the following way: 'Histogram is a graphical representation of the distribution of numerical data.' Absolutely true, and… totally unclear :) Well, let's think about histograms in another way.

Have you ever made a bar or column chart to represent some numerical data? I bet everyone has. A histogram is a specific use of a column chart where each column represents the frequency of elements in a certain range. In other words, a histogram graphically displays the number of elements within the consecutive non-overlapping intervals, or bins.

For example, you can make a histogram to display the number of days with a temperature between 61-65, 66-70, 71-75, etc. degrees, the number of sales with amounts between $100-$199, $200-$299, $300-$399, the number of students with test scores between 41-60, 61-80, 81-100, and so on.

The following screenshot gives an idea of how an Excel histogram can look like:

How to create a histogram in Excel using Analysis ToolPak

The Analysis ToolPak is a Microsoft Excel data analysis add-in, available in all modern versions of Excel beginning with Excel 2007. However, this add-in is not loaded automatically on Excel start, so you would need to load it first.

Load the Analysis ToolPak add-in

To add the Data Analysis add-in to your Excel, perform the following steps:

1. In Excel 2010, Excel 2013, Excel 2016, and Excel 2019, click File > Options. In Excel 2007, click the Microsoft Office button, and then click Excel Options.
2. In the Excel Options dialog, click Add-Ins on the left sidebar, select Excel Add-ins in the Manage box, and click the Go button.
   
3. In the Add-Ins dialog box, check the Analysis ToolPak box, and click OK to close the dialog.

   If Excel shows a message that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.
   

Now, the Analysis ToolPak is loaded in your Excel, and its command is available in the Analysis group on the Data tab.

Specify the Excel histogram bin range

Before creating a histogram chart, there is one more preparation to make - add the bins in a separate column.

Bins are numbers that represent the intervals into which you want to group the source data (input data). The intervals must be consecutive, non-overlapping and usually equal size.

Excel's Histogram tool includes the input data values in bins based on the following logic:

• A value is included in a certain bin if it is greater than the lowest bound and equal to or less than the greatest bound for that bin.
• If your input data contain any values greater than the highest bin, all such numbers will be included in the More category.
• If you do not specify the bin range, Excel will create a set of evenly distributed bins between the minimum and maximum values of your input data range.

Considering the above, type the bin numbers that you want to use in a separate column. The bins must be entered in ascending order, and your Excel histogram bin range should be limited to the input data range.

In this example, we have order numbers in column A and estimated delivery in column B. In our Excel histogram, we want to display the number of items delivered in 1-5 days, 6-10 days, 11-15 days, 16-20 days and over 20 days. So, in column D, we enter the bin range from 5 to 20 with an increment of 5 as shown in the below screenshot:

Make a histogram using Excel's Analysis ToolPak

With the Analysis ToolPak enabled and bins specified, perform the following steps to create a histogram in your Excel sheet:

1. On the Data tab, in the Analysis group, click the Data Analysis button.
   
2. In the Data Analysis dialog, select Histogram and click OK.
   
3. In the Histogram dialog window, do the following:
   • Specify the Input range and the Bin range.

     To do this, you can place the cursor in the box, and then simply select the corresponding range on your worksheet using the mouse. Alternatively, you can click the Collapse Dialog button , select the range on the sheet, and then click the Collapse Dialog button again to return to the Histogram dialog box.

     Tip. If you included column headers when selecting the input data and bin range, select the Labels check box.
   • Select the Output options.

     To place the histogram on the same sheet, click Output Range, and then enter the upper-left cell of the output table.

     To paste the output table and histogram in a new sheet or a new workbook, select New Worksheet Ply or New Workbook, respectively.

     Finally, choose any of the additional options:

     • To present data in the output table in descending order of frequency, select the Pareto (sorted histogram) box.
     • To include a cumulative percentage line in your Excel histogram chart, select the Cumulative Percentage box.
     • To create an embedded histogram chart, select the Chart Output box.

   For this example, I've configured the following options:
   

4. And now, click OK, and review the output table and histogram graph:
   

As you've just seen, it's very easy to make a histogram in Excel using the Analysis ToolPak. However, this method has a significant limitation - the embedded histogram chart is static, meaning that you will need to create a new histogram every time the input data is changed.

To make an automatically updatablehistogram, you can either use Excel functions or build a PivotTable as demonstrated below.

How to make a histogram in Excel using formulas

Another way to create a histogram in Excel is using the FREQUENCY or COUNTIFS function. The biggest advantage of this approach is that you won't have to re-do your histogram with each change in the input data. Like a normal Excel chart, your histogram will update automatically as soon as you edit, add new or delete existing input values.

To begin with, arrange your source data in one column (column B in this example), and enter the bin numbers in another column (column D), like in the screenshot below:

Now, we will use a Frequency or Countifs formula to calculate how many values fall into the specified ranges (bins), and then, we will draw a histogram based on that summary data.

Creating a histogram using Excel's FREQUENCY function

The most obvious function to create a histogram in Excel is the FREQUENCY function that returns the number of values that fall within specific ranges, ignoring text values and blank cells.

The FREQUENCY function has the following syntax:

• Data_array - a set of values for which you want to count frequencies.
• Bins_array - an array of bins for grouping the values.

In this example, the data_array is B2:B40, bin array is D2:D8, so we get the following formula:

=FREQUENCY(B2:B40,D2:D8)

Please keep in mind that FREQUENCY is a very specific function, so follow these rules to make it work right:

• An Excel Frequency formula should be entered as a multi-cell array formula. First, select a range of adjacent cells where you want to output the frequencies, then type the formula in the formula bar, and press Ctrl + Shift + Enter to complete it.
• It's recommended to enter one more Frequency formula than the number of bins. The extra cell is required to display the count of values above the highest bin. For the sake of clarity, you can label it 'More' like in the following screenshot (but don't include that 'More' cell in your bins_array!):

Like the Histogram option of the Analysis ToolPak, the Excel FREQUENCY function returns values that are greater than a previous bin and less than or equal to a given bin. The last Frequency formula (in cell E9) returns the number of values greater than the highest bin (i.e. the number of delivery days over 35).

To make things easier to understand, the following screenshot shows the bins (column D), corresponding intervals (column C), and computed frequencies (column E):

Online Data Analysis Tool

Making a histogram using COUNTIFS function

Another function that can help you calculate frequency distributions to plot histogram in Excel is COUNTIFS. And in this case, you will need to use 3 different formulas:

• The formula for the first cell - top bin (F2 in the screenshot below):
  =COUNTIFS($B$2:$B$40,'<='&$D2)

  The formula counts how many values in column B are less than the smallest bin in cell D2, i.e. returns the number of items delivered within 1-5 days.

• The formula for the last cell - over the highest bin (F9 in the screenshot below):
  =COUNTIFS($B$2:$B$100,'>'&$D8)

  The formula counts how many values in column B are greater than the highest bin in D8.

• The formula for remaining bins (cells F3:F8 in the screenshot below):
  =COUNTIFS($B$2:$B$40,'>'&$D2,$B$2:$B$40,'<='&$D3)

Analysis Tool Pack For Excel 2010

The formula counts the number of values in column B that are greater than the bin in the above row and less than or equal to the bin in the same row.

I have a windows 2008 R2 SP1 standard dedicated server and i installed Hyper-V role on it. I'm serving Hyper-V VPSs on this server and i can't trust virtual nodes. Mac address spoofing tool for windows 8.

As you see, the FREQUENCY and COUNTIFS functions return identical results:

'What is the reason of using three different formulas instead of one?' you may ask me. Basically, you get rid of the multi-cell array formula and can add and delete bins easily.

Make a histogram based on the summary data

Now that you have a list of frequency distributions computed with either FREQUENCY or COUNTIFS function, create a usual bar chart - select the frequencies, switch to the Insert tab and click the 2-D Column chart in the Charts group:

The bar graph will be immediately inserted in your sheet:

Generally speaking, you already have a histogram for your input data, though it definitely requires a few improvements. Most importantly, to make your Excel histogram easy to understand, you need to replace the default labels of the horizontal axis represented by serial numbers with your bin numbers or ranges.

The easiest way is to type the ranges in a column left to the column with the Frequency formula, select both columns - Ranges and Frequencies - and then create a bar chart. The ranges will be automatically used for the X axis labels, as shown in the below screenshot:

If there is no way you can type the desired histogram labels on your sheet, then you can enter them directly on the chart, independently of the worksheet data. The final part of this tutorial explains how to do this, and shows a couple of other improvements that can be made to your Excel histogram.

How to make a histogram with a PivotChart

As you may have noticed in the two previous examples, the most time-consuming part of creating a histogram in Excel is calculating the number of items within each bin. Once the source data has been grouped, an Excel histogram chart is fairly easy to draw.

As you probably know, one of the fastest ways to automatically summarize data in Excel is a PivotTable. So, let's get to it and plot a histogram for the Delivery data (column B):

1. Create a pivot table

To create a pivot table, go to the Insert tab > Tables group, and click PivotTable. And then, move the Delivery field to the ROWS area, and the other field (Order no. in this example) to the VALUES area, as shown in the below screenshot.

If you have not dealt with Excel pivot tables yet, you may find this tutorial helpful: Excel PivotTable tutorial for beginners.

2. Summarize values by Count

By default, numeric fields in a PivotTable are summed, and so is our Order numbers column, which makes absolutely no sense :) Anyway, because for a histogram we need a count rather than sum, right-click any order number cell, and select Summarize Values By > Count.

Now, your updated PivotTable should look like this:

3. Create the intervals (bins)

Analysis Tools For Business

The next step is to create the intervals, or bins. For this, we will be using the Grouping option. Right-click any cell under Row Labels in your pivot table, and select Group…

In the Grouping dialog box, specify the starting and ending values (usually Excel enters the minimum and maximum value automatically based on your data), and type the desired increment (interval length) in the By box.

In this example, the minimum delivery time is 1 day, maximum - 40 days, and the increment is set to 5 days:

Click OK, and your pivot table will display the intervals as specified:

4. Plot a histogram

One final step is left - draw a histogram. To do this, simply create a column pivot chart by clicking the PivotChart on the Analyze tab in PivotTable Tools group:

And the default column PivotChart will appear in your sheet straight away:

And now, polish up your histogram with a couple of finishing touches:

• Delete the legend by clicking the Chart Elements button and removing the tick from the Legend Or, select the legend on the histogram and press the Delete key on your keyboard.
• Replace the default Total title with something more meaningful.
• Optionally, choose another chart style in the Chart Styles group on the PivotChart Tools > Design tab.
• Remove the chart buttons by clicking Field Buttons on the PivotChart Tools > Analyze tab, in the Show/Hide group:

Additionally, you may want to achieve a conventional histogram look where bars touch each other. And you will find the detailed guidance on how to do this in the next and final part of this tutorial.

Customize and improve your Excel histogram

Whether you create a histogram using the Analysis ToolPak, Excel functions or a PivotChart, you might often want to customize the default chart to your liking. We have a special tutorial about Excel charts that explains how to modify the chart title, legend, axes titles, change the chart colors, layout and style. And here, we will discuss a couple of major customizations specific to an Excel histogram.

Change the axis labels on an Excel histogram chart

When creating a histogram in Excel with the Analysis ToolPak, Excel adds the horizontal axis labels based on the bin numbers that you specify. But what if, on your Excel histogram graph, you want to display ranges instead of bin numbers? For this, you'd need to change the horizontal axis labels by performing these steps:

1. Right-click the category labels in the X axis, and click Select Data…
   
2. On the right-hand side pane, under Horizontal (Category) Axis Labels, click the Edit button.
   
3. In the Axis label range box, enter the labels you want to display, separated by commas. If you are entering the intervals, enclose them in double quotes like in the following screenshot:
   
4. Click OK. Done!

Remove spacing between bars

When making a histogram in Excel, people often expect adjacent columns to touch each other, without any gaps. This is an easy thing to fix. To eliminate empty space between the bars, just follow these steps:

1. Select the bars, right-click, and choose Format Data Series…
   
2. On the Format Data Series pane, set the Gap Width to zero:
   

And voila, you have plotted an Excel histogram with bars touching each other:

Mining target. Merge Add-in for Excel for the Mac. RDBMerge is a user friendly way to Merge Data from Multiple Excel workbooks into a Summary Workbook. Download add-in for Excel 2011 or 2016. Note: RDBMerge is available as freeware. But you can use the Contact button in the menu if you want to support the development of this merge add-in. Click the worksheet that contains the data you want to consolidate, select the data, and then click the Expand Dialog button on the right to return to the Consolidate dialog. If a worksheet containing data that you need to consolidate is in another workbook, click Browse to locate that workbook. Excel's Consolidate feature's claim to fame is merging and summarizing values from multiple workbooks. It's a great tool for combining data when several users work with different instances of the. Just get it to your Excel for Mac: go to the App Store -> click Free -> Install App. Open your tables and click through 5 simple steps: Select your Master and Lookup Tables. Tick off the columns for comparison or key columns that are the same in both worksheets. Merge Excel, CSV, or Text Files in Seconds Easy-to-use desktop software that will merge your files Bulk File Merger is a desktop program that will quickly merge multiple files on your Mac.

Data Analysis Tools For Excel

And then, you can embellish your Excel histogram further by modifying the chart title, axes titles, and changing the chart style or colors. For example, your final histogram may look something like this:

This is how you draw a histogram in Excel. For better understanding of the examples discussed in this tutorial, you can download the sample Excel Histogram sheet with source data and histogram charts. I thank you for reading and hope to see you on our blog next week.

Analysis Tools For Excel

You may also be interested in: