How to do pivot tables in excel 2013
![how to do pivot tables in excel 2013 how to do pivot tables in excel 2013](https://www.dummies.com/wp-content/uploads/353165.image0.jpg)
This is definitely a feature worth exploring when regular pivot tables just won’t cut it.This course is designed to provide you with basic working knowledge for using Excel spreadsheets for Data Analysis. With the data model we get many new calculation options that regular pivot tables just don’t have, including concatenating text values to display in the Values area. They are limited though, especially when it comes to displaying text values in the Values area. Pivot tables have been a feature in Excel for a long time and they can do a lot of great useful calculations. We can click and drag the Course List measure into the Values area of our pivot table and this will produce a comma separated list of a students courses. Measures can easily be identified from the data fields by the fx icon in from of the measure name. This new measure will appear listed in the PivotTable Fields window with all the other fields and we can use this new measure just like any of the other fields. This will take the Course field from the StudentData table and concatenate its values together with a comma and space character as a delimiter. We need to write the above formula into the DAX formula editor and then we can create the new measure by pressing the OK button. To create a measure that aggregates text into a comma separated list, we’re going to use the ConcatenateX DAX function. We can assign formatting to the measure.We can write our formula in the DAX formula editor.
![how to do pivot tables in excel 2013 how to do pivot tables in excel 2013](https://www.itprotoday.com/sites/itprotoday.com/files/styles/article_featured_retina/public/uploads/2014/06/mark-date-table_0.png)
![how to do pivot tables in excel 2013 how to do pivot tables in excel 2013](https://static.javatpoint.com/ms/excel/images/how-to-create-a-pivot-table-in-excel.png)
This will open the Measure dialog box where we can create our DAX formulas. To add a Measure, select the pivot table ➜ right click on the table of data found in the PivotTable Fields window ➜ choose Add Measure from the menu. We can create just about any calculation we can imagine with these. With the data model, we get access to a whole new world of possible calculations using DAX formulas. They come predefined with basic sum, count, average, minimum, maximum, standard deviation and variance calculations. With traditional pivot tables, we don’t need to define any calculations. In order to use DAX formulas, we will need to select the Add this to the Data Model option. Select a cell inside the data ➜ go to the Insert tab ➜ then press the Pivot Table button. A student can have multiple rows of data when they are enrolled in multiple courses.Ĭan we summarize this data with a pivot table so that we just display each student once and then show a comma separated list of their courses? Insert A Pivot Tableįirst, we will need to insert a pivot table. Here we’ve got a list of students along with the courses they are enrolled in.
![how to do pivot tables in excel 2013 how to do pivot tables in excel 2013](https://webhelpboard.com/wp-content/uploads/2017/10/excel-pivot-tables-starting-3-720x720.jpg)
#How to do pivot tables in excel 2013 mac
Sorry, but these modern features aren’t available in the Mac versions yet.
#How to do pivot tables in excel 2013 Pc
Traditional pivot tables do not have this functionality.Īlso, we will need to be a PC user with Excel 2013 (or later) and Office 365. The answer is yes, but we will need to use the data model and DAX formulas to do this. Is there any way to summarize text based data that will return text as the result? This is usually the area where we summarize fields by various different aggregation methods like taking the sum, average, minimum, maximum or standard deviation.īut the thing is, these aggregation methods require numeric data! They were wondering if they could have text values in the Values area of a pivot table? I got a rather interesting question from someone who attended one of my pivot table webinars.