{"id":1279,"date":"2017-01-28T15:14:00","date_gmt":"2017-01-28T14:14:00","guid":{"rendered":"http:\/\/www.ybierling.com\/v2\/?p=1279"},"modified":"2018-09-10T19:26:20","modified_gmt":"2018-09-10T17:26:20","slug":"how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel","status":"publish","type":"post","link":"https:\/\/www.ybierling.com\/v2\/en\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel\/","title":{"rendered":"How to count the number of occurrences in a list with a pivot table in Excel"},"content":{"rendered":"<p>Having a list of <a class=\"zem_slink\" title=\"Value (ethics)\" href=\"http:\/\/en.wikipedia.org\/wiki\/Value_%28ethics%29\" target=\"_blank\" rel=\"noopener wikipedia\">values<\/a>, for example city names, and want to know how many times each unique entry is repeated ?<\/p>\n<blockquote>\n<figure id=\"attachment_1284\" aria-describedby=\"caption-attachment-1284\" style=\"width: 124px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-list-of-values-with-multiple-occurences.png\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" class=\"wp-image-1284 size-full\" title=\"\" src=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-list-of-values-with-multiple-occurences.png\" alt=\"How to count the number of occurrences in a list with a pivot table in Excel : List of values with multiple occurences\" width=\"124\" height=\"204\" \/><\/a><figcaption id=\"caption-attachment-1284\" class=\"wp-caption-text\">List of values with multiple occurrences<\/figcaption><\/figure><\/blockquote>\n<p>Here&#8217;s a simple solution, using a pivot table.<\/p>\n<p><a href=\"http:\/\/www.ybierling.com\/v2\/en\/2017\/01\/29\/how-to-count-the-number-of-occurrences-in-a-list-with-functions-in-excel\/\">See also with standard functions<\/a><\/p>\n<p>Start by selecting your list of values, and go to menu <span style=\"color: #339966;\"><em><a class=\"zem_slink\" title=\"Insert (SQL)\" href=\"http:\/\/en.wikipedia.org\/wiki\/Insert_%28SQL%29\" target=\"_blank\" rel=\"noopener wikipedia\">INSERT<\/a><\/em> <\/span>&gt; <em><span style=\"color: #339966;\"><a class=\"zem_slink\" title=\"Pivot table\" href=\"http:\/\/en.wikipedia.org\/wiki\/Pivot_table\" target=\"_blank\" rel=\"noopener wikipedia\">PivotTable<\/a><\/span><\/em><\/p>\n<blockquote>\n<figure id=\"attachment_1285\" aria-describedby=\"caption-attachment-1285\" style=\"width: 202px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-creation-of-a-pivot-table.png\" target=\"_blank\" rel=\"noopener\"><img fetchpriority=\"high\" decoding=\"async\" class=\"wp-image-1285 size-medium\" title=\"\" src=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-creation-of-a-pivot-table-202x300.png\" alt=\"How to count the number of occurrences in a list with a pivot table in Excel : Creation of a pivot table\" width=\"202\" height=\"300\" srcset=\"https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-creation-of-a-pivot-table-202x300.png 202w, https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-creation-of-a-pivot-table.png 230w\" sizes=\"(max-width: 202px) 100vw, 202px\" \/><\/a><figcaption id=\"caption-attachment-1285\" class=\"wp-caption-text\">Creation of a pivot table<\/figcaption><\/figure><\/blockquote>\n<p>Make sure your selection is correct, and eventually select in which sheet you want the pivot table to be generated.<\/p>\n<blockquote>\n<figure id=\"attachment_1286\" aria-describedby=\"caption-attachment-1286\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-create-pivottable-options.png\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" class=\"wp-image-1286 size-medium\" src=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-create-pivottable-options-300x249.png\" alt=\"How to count the number of occurrences in a list with a pivot table in Excel : Create PivotTable options\" width=\"300\" height=\"249\" srcset=\"https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-create-pivottable-options-300x249.png 300w, https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-create-pivottable-options.png 401w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-1286\" class=\"wp-caption-text\">Create PivotTable options<\/figcaption><\/figure><\/blockquote>\n<p>Here you are with a pivot table &#8211; in below example only with one column<\/p>\n<blockquote>\n<figure id=\"attachment_1280\" aria-describedby=\"caption-attachment-1280\" style=\"width: 277px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-blank-pivot-table.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-1280 size-medium\" src=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-blank-pivot-table-277x300.png\" alt=\"How to count the number of occurrences in a list with a pivot table in Excel : Blank pivot table\" width=\"277\" height=\"300\" srcset=\"https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-blank-pivot-table-277x300.png 277w, https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-blank-pivot-table.png 652w\" sizes=\"(max-width: 277px) 100vw, 277px\" \/><\/a><figcaption id=\"caption-attachment-1280\" class=\"wp-caption-text\">Blank pivot table<\/figcaption><\/figure><\/blockquote>\n<p><a class=\"zem_slink\" title=\"Drag and drop\" href=\"http:\/\/en.wikipedia.org\/wiki\/Drag_and_drop\" target=\"_blank\" rel=\"noopener wikipedia\">Drag and drop<\/a> the name of the column with the values you want to count to <em><span style=\"color: #339966;\">ROWS<\/span><\/em><\/p>\n<blockquote>\n<figure id=\"attachment_1281\" aria-describedby=\"caption-attachment-1281\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-row-selection-drag-and-drop.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-1281 size-medium\" title=\"\" src=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-row-selection-drag-and-drop-300x249.png\" alt=\"How to count the number of occurrences in a list with a pivot table in Excel : Row selection drag and drop\" width=\"300\" height=\"249\" srcset=\"https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-row-selection-drag-and-drop-300x249.png 300w, https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-row-selection-drag-and-drop.png 648w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-1281\" class=\"wp-caption-text\">Row selection drag and drop<\/figcaption><\/figure><\/blockquote>\n<p>Repeat the operation, drag and drop the column name to <span style=\"color: #339966;\"><em>VALUES<\/em><\/span><\/p>\n<blockquote>\n<figure id=\"attachment_1282\" aria-describedby=\"caption-attachment-1282\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-value-selection-drag-and-drop.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-1282 size-medium\" title=\"\" src=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-value-selection-drag-and-drop-300x249.png\" alt=\"How to count the number of occurrences in a list with a pivot table in Excel : Value selection drag and drop\" width=\"300\" height=\"249\" srcset=\"https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-value-selection-drag-and-drop-300x249.png 300w, https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-value-selection-drag-and-drop.png 649w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-1282\" class=\"wp-caption-text\">Value selection drag and drop<\/figcaption><\/figure><\/blockquote>\n<p>Make sure the <span style=\"color: #339966;\"><em><a class=\"zem_slink\" title=\"Count\" href=\"http:\/\/en.wikipedia.org\/wiki\/Count\" target=\"_blank\" rel=\"noopener wikipedia\">Count<\/a> of<\/em><\/span> option is selected, and voil\u00e0 ! You know how many times each unique values appear in your list.<\/p>\n<blockquote>\n<figure id=\"attachment_1283\" aria-describedby=\"caption-attachment-1283\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-value-type-selection-and-result.png\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-1283 size-medium\" src=\"http:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-value-type-selection-and-result-300x249.png\" alt=\"How to count the number of occurrences in a list with a pivot table in Excel : Value type selection and result\" width=\"300\" height=\"249\" srcset=\"https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-value-type-selection-and-result-300x249.png 300w, https:\/\/www.ybierling.com\/v2\/wp-content\/uploads\/2017\/01\/how-to-count-the-number-of-occurrences-in-a-list-with-a-pivot-table-in-excel-value-type-selection-and-result.png 649w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-1283\" class=\"wp-caption-text\">Value type selection and result<\/figcaption><\/figure><\/blockquote>\n<h3 class=\"zemanta-related-title\" style=\"margin: 0 0 10px 0; padding: 0; clear: both;\">Related articles across the web<\/h3>\n<ul class=\"zemanta-article-ul zemanta-article-ul-image\" style=\"margin: 0; padding: 0; overflow: hidden;\">\n<li class=\"zemanta-article-ul-li-image zemanta-article-ul-li\" style=\"padding: 0; background: none; list-style: none; display: block; float: left; vertical-align: top; text-align: left; width: 104px; font-size: 12px; margin: 0 5px 10px 0;\"><a style=\"padding: 2px; display: block; text-decoration: none;\" href=\"https:\/\/www.techinasia.com\/add2store-ecommerce-global\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" style=\"border-radius: 3px; box-shadow: 0 1px 4px rgba(0, 0, 0, 0.2); padding: 0; margin: 0; border: 0; display: block; width: 100px; max-width: 100%;\" src=\"http:\/\/i.zemanta.com\/noimg_0_150_150.jpg\" \/><\/a><a style=\"display: block; overflow: hidden; text-decoration: none; line-height: 12pt; height: 80px; padding: 5px 2px 0 2px;\" href=\"https:\/\/www.techinasia.com\/add2store-ecommerce-global\" target=\"_blank\" rel=\"noopener\">This startup lets anyone sell online from anywhere, without storing anything<\/a><\/li>\n<li class=\"zemanta-article-ul-li-image zemanta-article-ul-li\" style=\"padding: 0; background: none; list-style: none; display: block; float: left; vertical-align: top; text-align: left; width: 104px; font-size: 12px; margin: 0 5px 10px 0;\"><a style=\"padding: 2px; display: block; text-decoration: none;\" href=\"https:\/\/techcrunch.com\/2016\/11\/30\/google-launches-app-maker\/?ncid=rss\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" style=\"border-radius: 3px; box-shadow: 0 1px 4px rgba(0, 0, 0, 0.2); padding: 0; margin: 0; border: 0; display: block; width: 100px; max-width: 100%;\" src=\"http:\/\/i.zemanta.com\/376162880_150_150.jpg\" \/><\/a><a style=\"display: block; overflow: hidden; text-decoration: none; line-height: 12pt; height: 80px; padding: 5px 2px 0 2px;\" href=\"https:\/\/techcrunch.com\/2016\/11\/30\/google-launches-app-maker\/?ncid=rss\" target=\"_blank\" rel=\"noopener\">Google launches App Maker<\/a><\/li>\n<li class=\"zemanta-article-ul-li-image zemanta-article-ul-li\" style=\"padding: 0; background: none; list-style: none; display: block; float: left; vertical-align: top; text-align: left; width: 104px; font-size: 12px; margin: 0 5px 10px 0;\"><a style=\"padding: 2px; display: block; text-decoration: none;\" href=\"http:\/\/www.business2community.com\/email-marketing\/growing-business-drip-email-campaigns-01733089\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" style=\"border-radius: 3px; box-shadow: 0 1px 4px rgba(0, 0, 0, 0.2); padding: 0; margin: 0; border: 0; display: block; width: 100px; max-width: 100%;\" src=\"http:\/\/i.zemanta.com\/376556427_150_150.jpg\" \/><\/a><a style=\"display: block; overflow: hidden; text-decoration: none; line-height: 12pt; height: 80px; padding: 5px 2px 0 2px;\" href=\"http:\/\/www.business2community.com\/email-marketing\/growing-business-drip-email-campaigns-01733089\" target=\"_blank\" rel=\"noopener\">Growing Your Business with Drip Email Campaigns<\/a><\/li>\n<li class=\"zemanta-article-ul-li-image zemanta-article-ul-li\" style=\"padding: 0; background: none; list-style: none; display: block; float: left; vertical-align: top; text-align: left; width: 104px; font-size: 12px; margin: 0 5px 10px 0;\"><a style=\"padding: 2px; display: block; text-decoration: none;\" href=\"https:\/\/dottech.org\/203609\/how-to-nest-a-table-within-a-table-in-word\/\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" style=\"border-radius: 3px; box-shadow: 0 1px 4px rgba(0, 0, 0, 0.2); padding: 0; margin: 0; border: 0; display: block; width: 100px; max-width: 100%;\" src=\"http:\/\/i.zemanta.com\/376853532_150_150.jpg\" \/><\/a><a style=\"display: block; overflow: hidden; text-decoration: none; line-height: 12pt; height: 80px; padding: 5px 2px 0 2px;\" href=\"https:\/\/dottech.org\/203609\/how-to-nest-a-table-within-a-table-in-word\/\" target=\"_blank\" rel=\"noopener\">How to nest a table within a table in Word [Tip]<\/a><\/li>\n<li class=\"zemanta-article-ul-li-image zemanta-article-ul-li\" style=\"padding: 0; background: none; list-style: none; display: block; float: left; vertical-align: top; text-align: left; width: 104px; font-size: 12px; margin: 0 5px 10px 0;\"><a style=\"padding: 2px; display: block; text-decoration: none;\" href=\"https:\/\/www.neowin.net\/news\/microsoft-rolls-out-windows-10-for-pcs-build-14393479-to-non-insiders---heres-whats-new\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" style=\"border-radius: 3px; box-shadow: 0 1px 4px rgba(0, 0, 0, 0.2); padding: 0; margin: 0; border: 0; display: block; width: 100px; max-width: 100%;\" src=\"http:\/\/i.zemanta.com\/376327379_150_150.jpg\" \/><\/a><a style=\"display: block; overflow: hidden; text-decoration: none; line-height: 12pt; height: 80px; padding: 5px 2px 0 2px;\" href=\"https:\/\/www.neowin.net\/news\/microsoft-rolls-out-windows-10-for-pcs-build-14393479-to-non-insiders---heres-whats-new\" target=\"_blank\" rel=\"noopener\">Microsoft rolls out Windows 10 for PCs build 14393.479 to non-Insiders &#8211; here&#8217;s what&#8217;s new<\/a><\/li>\n<li class=\"zemanta-article-ul-li-image zemanta-article-ul-li\" style=\"padding: 0; background: none; list-style: none; display: block; float: left; vertical-align: top; text-align: left; width: 104px; font-size: 12px; margin: 0 5px 10px 0;\"><a style=\"padding: 2px; display: block; text-decoration: none;\" href=\"http:\/\/www.telegraph.co.uk\/connect\/small-business\/business-solutions\/how-to-sell-online-ways-to-get-started-at-your-sme\/\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" style=\"border-radius: 3px; box-shadow: 0 1px 4px rgba(0, 0, 0, 0.2); padding: 0; margin: 0; border: 0; display: block; width: 100px; max-width: 100%;\" src=\"http:\/\/i.zemanta.com\/376807550_150_150.jpg\" \/><\/a><a style=\"display: block; overflow: hidden; text-decoration: none; line-height: 12pt; height: 80px; padding: 5px 2px 0 2px;\" href=\"http:\/\/www.telegraph.co.uk\/connect\/small-business\/business-solutions\/how-to-sell-online-ways-to-get-started-at-your-sme\/\" target=\"_blank\" rel=\"noopener\">How to sell online &#8211; ways to get started at your SME<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Having a list of values, for example city names, and want to know how many times each unique entry is<\/p>\n","protected":false},"author":1,"featured_media":418,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[566],"tags":[894,1851,33,303,792,1484,1357,1849,1853,892,1042,1855,798,244],"class_list":["post-1279","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-various","tag-column","tag-count","tag-en","tag-excel","tag-list","tag-msoffice","tag-option","tag-pivot","tag-row","tag-sheet","tag-table","tag-unique","tag-values","tag-various"],"_links":{"self":[{"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/posts\/1279","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/comments?post=1279"}],"version-history":[{"count":5,"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/posts\/1279\/revisions"}],"predecessor-version":[{"id":2610,"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/posts\/1279\/revisions\/2610"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/media\/418"}],"wp:attachment":[{"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/media?parent=1279"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/categories?post=1279"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ybierling.com\/v2\/wp-json\/wp\/v2\/tags?post=1279"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}