Note: the Analysis TookPak is no longer included in Excel for the Mac. You will need to have the Data Analysis add-in installed to your version of Excel to run. These tasks do not require the Analysis ToolPak or StatPlus:mac LE. Everything can be done easily with the out-of-the-package copy of Excel. Data tab → Data Analysis → Correlation. Highlight all the columns containing variables you. Click the Output Range: and select a place for the output by clicking on a cell.
Advertisement The pivot table is one of the single most 'Get that report to me, ASAP!' A core set of Microsoft Excel and Word tricks can make this task less stressful and help you whiz up a stylish report in no time. It is frequently used for large data analysis. You can use the pivot table as an interactive data summarisation tool to automatically condense large datasets into a separate, concise table. You can use it to create an informative summary of a large dataset or make regional comparisons between brand sales.
Pivot tables do not have to be intimidating – far from it – and we are here to help you unlock one of the real hidden power tools of Excel. To demonstrate the power of pivot tables, we will be using the UK Government dataset 2013 UK Fleet Landings by ICES Rectangle which can be downloaded via the linked page No Longer Available, if you want to play along at home. Summarise All Your Data – Small Vs. Large Datasets How you organise and group your data can make vital differences in It's amazing how quickly information can spread from person to person these days thanks to the Internet and social media.
Is it possible to keep up? The short answer is yes - with a catch. In a small sample, as in our example below, it is easy to spot trends:. Directors are paid more. Creative Directors have lowest average wage. Post-grads earn, on average, £9,000 more than individuals with a college education.
Scaling up to a government dataset demands a different approach. What appears to be a colossal list of raw, unlinked data can actually hold the key to business success or failure. Pivot tables are suited to larger datasets and to work really well, they need to hit a few basic criteria:. Numeric Information: Your subtotals require this data. Duplicates: At least one column with duplicate data ranges.
Duplicate data ranges allow us to more effectively filter and analyse our information, providing numerical data ranges for the duplicate sources. Import Raw Data Into a Pivot Table Luckily for us, contains the most up-to-date pivot table tool, now even offering up suggested pivot tables across the full spectrum of your available dataset. However, where the presets do not fit the bill, you can always manually select the pivot table wizard to get you rolling.
Clicking ‘Pivot Table’ should then bring you to this menu: Stick with the existing settings for now, though as you can see there are options to connect an external data source (i.e. MS Access), or place the pivot table within the existing worksheet.
For the latter option be sure to check that your new pivot table will not obscure or break your existing data (though you can always It happens even to the most experienced of users: the accidental keyboard shortcuts. No matter how often you use your computer, weird things can start happening when you’re going too fast, when you lean on. If something terrible does happen!).
So, clicking OK should present you with an entirely new worksheet for your brand spanking new pivot table. You should be: this is where the data magic happens!
If you are still with me, and I truly hope you are, your page should look like what is shown in the screenshot above, or something very similar if you chose a different Worksheet. Excel has very kindly sorted our Pivot Table fields so as we can work in two ways:. Drag and Drop: The pivot table fields listed on the right-hand side can be dragged into the 4 areas below (Filters, Columns, Rows and Values). You can select specific data items that you wish to cross-reference against any number of differentials. Add to Report: Clicking individual dataset fields will add them directly to the waiting report table (PivotTable2 in screenshot above). This method can be used to rapidly build, analyse, collate and modify the available data. Whilst both methods deliver similar outcomes, Add to Report allows you to simply select your preferred data field and marvel in wonder as your report table is transformed.
As a bonus, each time we click a new data field it is automatically added to the area Excel deems correct, whilst reminding us that we cannot add a data ‘field with more than 16384 items to the column area.’ Thanks, Excel, always looking out for the business person, aren’t you? Pick & Compare Data Fields Now, click Vessel Nationality, Species Name, Landed Weight (tonnes) and Value (£). Your data subtotals should now look like this: Whilst your pivot table fields should match this: We can now rapidly glance at our custom pivot table data fields and analyse the available data to ascertain any trends. As a result of our actions, we now know that England fishes the most Bass throughout the UK, though the fish itself is worth more in Wales. Insight gained! Filtering Your Data Feel free at this point to play around with the different fields, learn which varieties of data produce concise correlation, which data fields need tweaking to reveal their secrets and to comprehend the value actions of filter settings:.
Hover over the desired data field, note the small black arrowhead to the right of the text. Selecting the black arrowhead reveals a drop down ‘Filter’ menu – extremely useful for isolating data within a single field, or comparisons between similar data ranges across multiple data sources. Select the Vessel Nationality filter menu. By default, all data ranges are selected. Unselect all (by hitting select all), followed by reselecting England and Scotland.
This isolates the data for these countries, filling our pivot table with concise, comparative data we are able to begin to analyse for potential trends. Modifying the filters of each available field will almost always directly alter the data presented in the pivot table and is a sure fire method of What would you say if I told you that you have the tools at your disposal to do ground-breaking, Earth-shattering research? Well, you do, and I'll show you how. Size Matters For this example, we used a small proportion of a relatively large dataset. Pivot tables work best when they have several thousand individual records, multiple data fields and a range of duplicate numerical figures. That is not to say their relevance is strictly associated with large datasets but, when dealing with a massive amount of raw data, nothing can beat the pivot table in ease of use, data filtering or its concise summarising facilities. Has this ‘How To’ helped you, or have we missed something?
We’d like to hear your pivot table experiences below! Explore more about:.
Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Counting is an integral part of data analysis, whether you are tallying the head count of a department in your organization or the number of units that were sold quarter-by-quarter. Excel provides multiple techniques that you can use to count cells, rows, or columns of data. To help you make the best choice, this article provides a comprehensive summary of methods, a downloadable workbook with interactive examples, and links to related topics for further understanding. Note: Counting should not be confused with summing. For more information about summing values in cells, columns, or rows, see. Download our examples You can download an example workbook that gives examples to supplement the information in this article.
Most sections in this article will refer to the appropriate worksheet within the example workbook that provides examples and more information. In this article. Simple counting You can count the number of values in a range or table by using a simple formula, clicking a button, or by using a worksheet function. Excel can also display the count of the number of selected cells on the Excel status bar. See the video demo that follows for a quick look at using the status bar. Also, see the section for more information.
You can refer to the values shown on the status bar when you want a quick glance at your data and don't have time to enter formulas. Video: Count cells by using the Excel status bar Watch the following video to learn how to view count on the status bar. View count on the Excel status bar Use AutoSum Use AutoSum by selecting a range of cells that contains at least one numeric value. Then on the Formulas tab, click AutoSum Count Numbers. Excel returns the count of the numeric values in the range in a cell adjacent to the range you selected. Generally, this result is displayed in a cell to the right for a horizontal range or in a cell below for a vertical range. Add a Subtotal row You can add a subtotal row to your Excel data.
Click anywhere inside your data, and then click Data Subtotal. Note: The Subtotal option will only work on normal Excel data, and not Excel tables, PivotTables, or PivotCharts. Also, refer to the following articles:. Count cells in a list or Excel table column by using the SUBTOTAL function Use the function to count the number of values in an Excel table or range of cells.
If the table or range contains hidden cells, you can use SUBTOTAL to include or exclude those hidden cells, and this is the biggest difference between SUM and SUBTOTAL functions. The SUBTOTAL syntax goes like this: SUBTOTAL(functionnum,ref1,ref2.) To include hidden values in your range, you should set the functionnum argument to 2. To exclude hidden values in your range, set the functionnum argument to 102. Counting based on one or more conditions You can count the number of cells in a range that meet conditions (also known as criteria) that you specify by using a number of worksheet functions.
Video: Use the COUNT, COUNTIF, and COUNTA functions Watch the following video to see how to use the COUNT function and how to use the COUNTIF and COUNTA functions to count only the cells that meet conditions you specify. Note: A7 is a time value, but it contains text ( a.m.), hence COUNT does not consider it a numerical value. If you were to remove a.m. From the cell, COUNT will consider A7 as a numerical value, and change the output to 4. Count cells in a range based on a single condition by using the COUNTIF function Use the function to count how many times a particular value appears in a range of cells. Count cells in a column based on single or multiple conditions by using the DCOUNT function counts the cells that contain numbers in a field (column) of records in a list or database that match conditions that you specify.
In the following example, you want to find the count of the months including or later than March 2016 that had more than 400 units sold. The first table in the worksheet, from A1 to B7, contains the sales data. DCOUNT uses conditions to determine where the values should be returned from. Conditions are typically entered in cells in the worksheet itself, and you then refer to these cells in the criteria argument. In this example, cells A10 and B10 contain two conditions—one that specifies that the return value must be greater than 400, and the other that specifies that the ending month should be equal to or greater than March 31st, 2016. You should use the following syntax: =DCOUNT(A1:B7,'Month ending',A9:B10) DCOUNT checks the data in the range A1 through B7, applies the conditions specified in A10 and B10, and returns 2, the total number of rows that satisfy both conditions (rows 5 and 7).
Count cells in a range based on multiple conditions by using the COUNTIFS function The function is similar to the COUNTIF function with one important exception: COUNTIFS lets you apply criteria to cells across multiple ranges and counts the number of times all criteria are met. You can use up to 127 range/criteria pairs with COUNTIFS. The syntax for COUNTIFS is: COUNTIFS(criteriarange1, criteria1, criteriarange2, criteria2,) See the following example: Count based on criteria by using the COUNT and IF functions together Let's say you need to determine how many salespeople sold a particular item in a certain region or you want to know how many sales over a certain value were made by a particular salesperson. You can use the IF and COUNT functions together; that is, you first use the IF function to test a condition and then, only if the result of the IF function is True, you use the COUNT function to count cells.
Notes:. The formulas in this example must be entered as array formulas. If you have opened this workbook in Excel for Windows or Excel 2016 for Mac and want to change the formula or create a similar formula, press F2, and then press Ctrl+Shift+Enter to make the formula return the results you expect. In earlier versions of Excel for Mac, use +Shift+Enter. For the example formulas to work, the second argument for the IF function must be a number. Count how often multiple text or number values occur by using the SUM and IF functions together In the examples that follow, we use the IF and SUM functions together. The IF function first tests the values in some cells and then, if the result of the test is True, SUM totals those values that pass the test.
Example 1 The above function says if C2:C7 contains the values Buchanan and Dodsworth, then the SUM function should display the sum of records where the condition is met. The formula finds three records for Buchanan and one for Dodsworth in the given range, and displays 4. Example 2 The above function says if D2:D7 contains values lesser than $9000 or greater than $19,000, then SUM should display the sum of all those records where the condition is met.
The formula finds two records D3 and D5 with values lesser than $9000, and then D4 and D6 with values greater than $19,000, and displays 4. Example 3 The above function says if D2:D7 has invoices for Buchanan for less than $9000, then SUM should display the sum of records where the condition is met. The formula finds that C6 meets the condition, and displays 1.
Important: The formulas in this example must be entered as array formulas. That means you press F2 and then press Ctrl+Shift+Enter. In earlier versions of Excel for Mac use +Shift+Enter. See the following Knowledge Base articles for additional tips:. Count cells in a column or row in a PivotTable A PivotTable summarizes your data and helps you analyze and drill down into your data by letting you choose the categories on which you want to view your data.
You can quickly create a PivotTable by selecting a cell in a range of data or Excel table and then, on the Insert tab, in the Tables group, clicking PivotTable. Let's look at a sample scenario of a Sales spreadsheet, where you can count how many sales values are there for Golf and Tennis for specific quarters.
Note: For an interactive experience, you can run these steps on the sample data provided in the PivotTable sheet in the downloadable workbook. Enter the following data in an Excel spreadsheet. Select A2:C8. Click Insert PivotTable. In the Create PivotTable dialog box, click Select a table or range, then click New Worksheet, and then click OK. An empty PivotTable is created in a new sheet.
In the PivotTable Fields pane, do the following:. Drag Sport to the Rows area.
Drag Quarter to the Columns area. Drag Sales to the Values area. Repeat step c. The field name displays as SumofSales2 in both the PivotTable and the Values area. At this point, the PivotTable Fields pane looks like this:. In the Values area, click the dropdown next to SumofSales2 and select Value Field Settings. In the Value Field Settings dialog box, do the following:.
In the Summarize value field by section, select Count. In the Custom Name field, modify the name to Count. The PivotTable displays the count of records for Golf and Tennis in Quarter 3 and Quarter 4, along with the sales figures.
Counting when your data contains blank values You can count cells that either contain data or are blank by using worksheet functions. Count nonblank cells in a range by using the COUNTA function Use the function to count only cells in a range that contain values. When you count cells, sometimes you want to ignore any blank cells because only cells with values are meaningful to you.
For example, you want to count the total number of salespeople who made a sale (column D). COUNTA ignores the blank values in D3, D4, D8, and D11, and counts only the cells containing values in column D. The function finds six cells in column D containing values and displays 6 as the output.
Count nonblank cells in a list with specific conditions by using the DCOUNTA function Use the function to count nonblank cells in a column of records in a list or database that match conditions that you specify. The following example uses the DCOUNTA function to count the number of records in the database that is contained in the range A1:B7 that meet the conditions specified in the criteria range A9:B10. Those conditions are that the Product ID value must be greater than or equal to 2000 and the Ratings value must be greater than or equal to 50.
DCOUNTA finds two rows that meet the conditions- rows 2 and 4, and displays the value 2 as the output. Count blank cells in a contiguous range by using the COUNTBLANK function Use the function to return the number of blank cells in a contiguous range (cells are contiguous if they are all connected in an unbroken sequence). If a cell contains a formula that returns empty text ('), that cell is counted. When you count cells, there may be times when you want to include blank cells because they are meaningful to you. In the following example of a grocery sales spreadsheet. Suppose you want to find out how many cells don't have the sales figures mentioned. Note: The COUNTBLANK worksheet function provides the most convenient method for determining the number of blank cells in a range, but it doesn't work very well when the cells of interest are in a closed workbook or when they do not form a contiguous range.
The Knowledge Base article shows you how to use a SUM(IF) array formula in those cases. Count blank cells in a non-contiguous range by using a combination of SUM and IF functions Use a combination of the SUM function and the IF function.
In general, you do this by using the IF function in an array formula to determine whether each referenced cell contains a value, and then summing the number of FALSE values returned by the formula. See a few examples of SUM and IF function combinations in an earlier section in this topic. Counting unique occurrences of values You can count unique values in a range by using a, or the Advanced Filter dialog box. Count the number of unique values in a list column by using Advanced Filter Use the Advanced Filter dialog box to find the unique values in a column of data. You can either filter the values in place or you can extract and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range. To use Advanced Filter, click the Data tab, and in the Sort & Filter group, click Advanced.
The following figure shows how you use the Advanced Filter to copy only the unique records to a new location on the worksheet. In the following figure, column E contains the values that were copied from the range in column D.
Notes:. If you filter your data in place, values are not deleted from your worksheet — one or more rows might be hidden. Click Clear in the Sort & Filter group on the Data tab to display those values again. If you only want to see the number of unique values at a quick glance, select the data after you have used the Advanced Filter (either the filtered or the copied data) and then look at the status bar. The Count value on the status bar should equal the number of unique values. For more information, see Count the number of unique values in a range that meet one or more conditions by using IF, SUM, FREQUENCY, MATCH, and LEN functions Use various combinations of the IF, SUM, FREQUENCY, MATCH, and LEN functions.
For more information and examples, see the section 'Count the number of unique values by using functions' in the article. Also, see the article. Special cases (count all cells, count words) You can count the number of cells or the number of words in a range by using various combinations of worksheet functions. Count the total number of cells in a range by using ROWS and COLUMNS functions Suppose you want to determine the size of a large worksheet to decide whether to use manual or automatic calculation in your workbook. To count all the cells in a range, use a formula that multiplies the return values using the and functions. See the following image for an example: Count words in a range by using a combination of SUM, IF, LEN, TRIM, and SUBSTITUTE functions You can use a combination of the SUM, IF, LEN, TRIM, and SUBSTITUTE functions in an array formula. The following example shows the result of using a nested formula to find the number of words in a range of 7 cells (3 of which are empty).
Some of the cells contain leading or trailing spaces — the TRIM and SUBSTITUTE functions remove these extra spaces before any counting occurs. See the following example: Now, for the above formula to work correctly, you have to make this an array formula, otherwise the formula returns the #VALUE! To do that, click on the cell that has the formula, and then in the Formula bar, press Ctrl + Shift + Enter. Excel adds a curly bracket at the beginning and the end of the formula, thus making it an array formula.
For more information on array formulas, see. Displaying calculations and counts on the status bar When one or more cells are selected, information about the data in those cells is displayed on the Excel status bar. For example, if four cells on your worksheet are selected, and they contain the values 2, 3, a text string (such as 'cloud'), and 4, all of the following values can be displayed on the status bar at the same time: Average, Count, Numerical Count, Min, Max, and Sum.
Right-click the status bar to show or hide any or all of these values. These values are shown in the illustration that follows.
Do you have a specific Excel question? Help us improve Excel Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at.