Excel Tool Tutorial #5: PivotCharts and PivotTables

this is the fifth video in our series showing you how to use the CDC Excel tool for thematic analysis this video will review some Basics about using pivot tables and how to customize the built-in pivot charts and tables for your use once you've coded your data in the coding workspace you want to be able to look at some summaries of these codes so you can start by looking at the coding table sheet and the very first thing that you should always do when you're opening the coding tables or quote sheet is to refresh the data that's in them the information here is linked to the information from the coding workspace but this information does not update automatically so whenever you've made changes to the codeing workspace sheet you need to refresh the data for both this sheet and the quote sheet to update the information in the tables you're going to click anywhere in the table so that you have have this pivot table analyze option available to you you'll click on the pivot table analyze and then go to refresh and refresh all this will update all the tables in your workbook all the tables in the workbook should now contain the most up-to-date information from your coding on this sheet are two pre-built charts that show you the most frequent Broad and most frequent specific codes that were applied to the data so using the first chart we can see that the most frequently applied broad code was about people dealing with the epidemic and when we look at the chart we can see that 11 text segments had this broad code applied to them when we scroll over we can see the table and the chart of the specific codes these specific codes are organized by their broad codes and so as we scroll down we can see which specific codes were most frequently applied to the data so for example we can see that the strategy raising Community awareness about under about a government L control efforts had five tech segments that were coded with this code this coding tabulation sheet is very useful to give you the count of how many times each code was applied and is the first part of your results for the next step of the Thematic analysis we need to consider people's specific words using the codes as a way to preliminarily organize this information to do that we're going to move to the quote sheet on the quote sheet you'll find another pivot table that also contains information about the epidemic coding scheme with the broad topics listed here and the ability to hit these plus signs so that we can expand and look at the specific codes that fall under each of the broad codes so again we can see that 11 teex segments were were applied for about people dealing with the epidemic and then we can see that one Tech segment was applied with awareness level of awareness about epidemic and then under that specific code we can see the summary of all of the Tex segments that had that code applied to them there are multiple options of how you can customize this pivot table to help you look further into your data and answer your specific questions firstly you can use these boxes that are called slicers and we've already set up a few to filter the data so for example if we were only interested in what people had to say under the broad code about the disease we can click on this option right here about the disease and now we will our pivot table has been filtered to only show information that was coded about the disease you can also apply multiple categories as a filter in these slicers so for example if we wanted to also examine SE Ms coded as about the epidemic we would select this multi select option with the little check marks at the top and now we can select more than one category to show and so now you can see all of the all of the text segments that were coded as either about the disease or about the epidemic you can also easily remove these filters and go back to the full pivot table by hitting the clear filter button with the little red X to adjust the fields that are actually within your pivot table you want to use What's called the field list to find this again you need to make sure that you're clicked into the pivot table so that you have the pivot table analyze ribbon at the top select that and then select this field list option this will open another menu for you to choose from so you can see here that all of our column head from the coding workspace are available to us in this pivot table field so for example we might be interested in stratifying our codes by participant type and we can drag this participant type down into the rows option and now we can see that which codes were applied based on the type of participant who is giving this feedback so for example we can see that Community leaders we're most frequently talking about the government L control efforts and then we can scroll down to see that mothers were most frequently talking about people dealing with the epidemic we can easily move these these rows around so we could put the participant type in between the broad code and the specific code and we can also remove them here so to remove them you'll click on this little arrow and then remove field so now we're back to just having the broad codes and the specific codes once you've examined the results of your coding in these pivot tables you can move on to doing the Thematic analysis we will cover that in our next and final video