{"id":1131,"date":"2024-02-12T08:51:37","date_gmt":"2024-02-12T03:21:37","guid":{"rendered":"https:\/\/sheetstips.com\/?p=1131"},"modified":"2024-02-12T15:26:03","modified_gmt":"2024-02-12T09:56:03","slug":"calculated-fields-google-sheets","status":"publish","type":"post","link":"https:\/\/sheetstips.com\/calculated-fields-google-sheets\/","title":{"rendered":"How to Add &#038; Use Calculated Fields in Google Sheets Pivot Tables"},"content":{"rendered":"<p>Calculated Fields are very useful when it comes to summarising the data in Google Sheet <strong><a href=\"https:\/\/sheetstips.com\/insert-pivot-table-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Pivot tables<\/a><\/strong>. The main purpose of Calculated Fields is that it allows you to process your data in order to create more tailored Pivot table results. With the help of Custom formulas, one can present summary metrics in the Pivot table using calculated fields without making any changes in the actual dataset. In this article, let us understand how to add and use the Calculated Fields with the <strong><a href=\"https:\/\/sheetstips.com\/\">Google Sheet tips<\/a><\/strong> provided on this page. Read on to find more.<\/p>\n<p>Also Read: <a href=\"https:\/\/btechgeeks.com\/mining-engineering-notes\/\" target=\"_blank\" rel=\"noopener\">Mining Engineering Notes<\/a><\/p>\n<table>\n<tbody>\n<tr>\n<td width=\"601\">\n<p style=\"text-align: center;\"><strong>Table of Contents<\/strong><\/p>\n<ul>\n<li><a href=\"#How-to-Create-Pivot-Tables-in-Google-Sheets?\">How to Create Pivot Tables in Google Sheets?<\/a><\/li>\n<li><a href=\"#How-to-Add-Calculated-Fields-in-Google-Sheet?\">How to Add Calculated Fields in Google Sheet?<\/a><\/li>\n<li><a href=\"#Points-to-Note-About-Calculated-Fields-in-Google-Sheets\">Points to Note About Calculated Fields in Google Sheets<\/a><\/li>\n<\/ul>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-1097 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/Sheets-Tips.png\" alt=\"Sheets Tips\" width=\"449\" height=\"79\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/Sheets-Tips.png 512w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/Sheets-Tips-300x53.png 300w\" sizes=\"(max-width: 449px) 100vw, 449px\" \/><\/p>\n<h2 id=\"How-to-Create-Pivot-Tables-in-Google-Sheets?\">How to Create Pivot Tables in Google Sheets?<\/h2>\n<p>Let us consider the following dataset which represents the sales data of each division.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1132 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets.png\" alt=\"calculated-fields-google-sheets\" width=\"664\" height=\"436\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets.png 664w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets-300x197.png 300w\" sizes=\"(max-width: 664px) 100vw, 664px\" \/><\/p>\n<ul>\n<li><a href=\"https:\/\/sheetstips.com\/group-by-month-pivot-table-google-sheets\/\">How to Group by Month in Pivot Table in Google Sheets (With Examples)<\/a><\/li>\n<li><a href=\"https:\/\/sheetstips.com\/refresh-pivot-table-google-sheets\/\">How to Refresh Pivot Table in Google Sheets: 3 Ways to Refresh Pivot Tables<\/a><\/li>\n<li><a href=\"https:\/\/sheetstips.com\/insert-pivot-table-google-sheets\/\">How to Insert a Pivot Table in Google Sheets? (Create\/Edit\/Customize Pivot Tables)<\/a><\/li>\n<\/ul>\n<p>Now follow the steps listed below to create a Pivot table for the above dataset.<\/p>\n<ul>\n<li><strong>Step 1:<\/strong> Click on the <strong>Data<\/strong> tab from the menubar.<\/li>\n<li><strong>Step 2:<\/strong> Now choose &#8220;<strong>Pivot Table<\/strong>&#8221; from the drop-down menu.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1133 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets1.png\" alt=\"calculated-fields-google-sheets\" width=\"689\" height=\"520\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets1.png 689w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets1-300x226.png 300w\" sizes=\"(max-width: 689px) 100vw, 689px\" \/><\/p>\n<ul>\n<li><strong>Step 3:<\/strong> You should now see a window asking whether you want to place your pivot table into an existing sheet or create a new one. Choose your preferred option. It&#8217;s always advisable to make one in a new sheet for clarity.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1134 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets2.png\" alt=\"calculated-fields-google-sheets\" width=\"880\" height=\"393\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets2.png 880w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets2-300x134.png 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets2-768x343.png 768w\" sizes=\"(max-width: 880px) 100vw, 880px\" \/><\/p>\n<ul>\n<li><strong>Step 4:<\/strong> Click on the &#8220;<strong>Create<\/strong>&#8221; button.<\/li>\n<li><strong>Step 5:<\/strong> Now the Pivot table will be created in the new sheet. The Pivot table will look like the following image at this stage.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1135 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets3.png\" alt=\"calculated-fields-google-sheets\" width=\"600\" height=\"378\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets3.png 600w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets3-300x189.png 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/p>\n<ul>\n<li><strong>Step 6:<\/strong> A grid of \u2018<strong>Rows, Columns, and Values<\/strong>&#8216; should be displayed. You may now begin populating your pivot table with the information you desire. A Pivot Table Editor should appear on the right side of the window. This will assist you in determining what should be included in your pivot table.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1136 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets4.png\" alt=\"calculated-fields-google-sheets\" width=\"1110\" height=\"420\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets4.png 1110w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets4-300x114.png 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets4-1024x387.png 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets4-768x291.png 768w\" sizes=\"(max-width: 1110px) 100vw, 1110px\" \/><\/p>\n<h3 id=\"How-to-Add-Calculated-Fields-in-Google-Sheet?\">How to Add Calculated Fields in Google Sheet?<\/h3>\n<p>Now let us understand how to add the calculated fields using the SUM function in Google Sheets with the help of the steps given below:<\/p>\n<ul>\n<li><strong>Step 1:<\/strong> Select the pivot table by clicking on it.<\/li>\n<li><strong>Step 2:<\/strong> Click Add next to &#8220;<strong>Values<\/strong>&#8221; on the side panel.<\/li>\n<li><strong>Step 3:<\/strong> Select the Calculated field box from the drop-down menu.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1137 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets5.png\" alt=\"calculated-fields-google-sheets\" width=\"306\" height=\"529\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets5.png 306w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets5-174x300.png 174w\" sizes=\"(max-width: 306px) 100vw, 306px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li><strong>Step 4:<\/strong> To use SUM to calculate a value, click SUM next to &#8220;<strong>Summarize by.<\/strong>&#8220;<\/li>\n<li><strong>Step 5:<\/strong> To use a custom formula to calculate a value, follow these steps: Enter a formula in the field that displays. In our case the formula is <strong>=SUM(arrayformula(&#8216;Number of Units&#8217;*&#8217;Price Per Unit&#8217;))\/sum(&#8216;Number of Units&#8217;)<\/strong><\/li>\n<li><strong>Step 7:<\/strong> Then click Custom next to &#8220;<strong>Summarize by.<\/strong>&#8220;<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1138 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets6.png\" alt=\"calculated-fields-google-sheets\" width=\"294\" height=\"165\" \/><\/p>\n<ul>\n<li><strong>Step 8:<\/strong> Click Add in the bottom right corner to add a new column.<\/li>\n<\/ul>\n<p>Now you can see the results as shown below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1139\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets7.png\" alt=\"calculated-fields-google-sheets\" width=\"1104\" height=\"434\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets7.png 1104w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets7-300x118.png 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets7-1024x403.png 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/calculated-fields-google-sheets7-768x302.png 768w\" sizes=\"(max-width: 1104px) 100vw, 1104px\" \/><\/p>\n<h3 id=\"Points-to-Note-About-Calculated-Fields-in-Google-Sheets\">Points to Note About Calculated Fields in Google Sheets<\/h3>\n<p>Pivot tables with calculated fields have a lot more flexibility and versatility. It does, however, have some restrictions. As a result, some considerations must be made while creating calculated fields.<\/p>\n<ul>\n<li>Only cells from your original dataset are referenced in your computed field calculations. They can&#8217;t refer to the totals or subtotals in the pivot table.<\/li>\n<li>In the calculated field formulas, you must use the field names from your dataset. Individual cells cannot be referred to by their address or cell names.<\/li>\n<li>It&#8217;s critical that you use the correct variable name for each field in your formula. If your field name has more than one word with spaces between them, you must use single quotes to surround the variable name in the calculated field&#8217;s formula.<\/li>\n<\/ul>\n<p>Must Read:<\/p>\n<p><a href=\"https:\/\/paisaalgo.com\/gail-pivots-calculator\/\"><span data-sheets-root=\"1\" data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;Gift Index&quot;}\" data-sheets-userformat=\"{&quot;2&quot;:11137,&quot;3&quot;:{&quot;1&quot;:0},&quot;10&quot;:2,&quot;11&quot;:0,&quot;12&quot;:0,&quot;14&quot;:{&quot;1&quot;:2,&quot;2&quot;:0},&quot;16&quot;:11}\">Gift Index<\/span><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Calculated Fields are very useful when it comes to summarising the data in Google Sheet Pivot tables. The main purpose of Calculated Fields is that it allows you to process your data in order to create more tailored Pivot table results. With the help of Custom formulas, one can present summary metrics in the Pivot &#8230; <a title=\"How to Add &#038; Use Calculated Fields in Google Sheets Pivot Tables\" class=\"read-more\" href=\"https:\/\/sheetstips.com\/calculated-fields-google-sheets\/\" aria-label=\"More on How to Add &#038; Use Calculated Fields in Google Sheets Pivot Tables\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[33,34],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1360,"url":"https:\/\/sheetstips.com\/auto-suggested-pivot-tables-in-sheets\/","url_meta":{"origin":1131,"position":0},"title":"Auto Suggested Pivot Chart in Google Sheets: Parameters for Creating Pivot Table Using Explore","author":"Varsha","date":"May 15, 2022","format":false,"excerpt":"Google Sheets Pivot Table is one of the best features which helps to summarize or view a large number of data in a simpler format. Also, with the help of the Pivot table in Google Sheets, we can apply filters and perform various operations and view the results without making\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"auto-suggested-pivot-tables-in-sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/10\/image-5.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":937,"url":"https:\/\/sheetstips.com\/refresh-pivot-table-google-sheets\/","url_meta":{"origin":1131,"position":1},"title":"How to Refresh Pivot Table in Google Sheets: 3 Ways to Refresh Pivot Tables","author":"Varsha","date":"May 17, 2022","format":false,"excerpt":"Google Sheets Pivot Table is a dynamic approach to summarise vast volumes of data rapidly. A Pivot Table can be used to study numerical data in depth and to answer unexpected queries about your data. However in some instances, if even there is a modification in data, the Pivot table\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/09\/group-by-month-pivot-table-google-sheets5-1.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1165,"url":"https:\/\/sheetstips.com\/slicer-google-sheets\/","url_meta":{"origin":1131,"position":2},"title":"How to Use Slicer in Pivot Tables in Google Sheets (Add\/Delete\/Customize Slicers)","author":"Varsha","date":"May 16, 2022","format":false,"excerpt":"Slicers in Google Sheets are an effective tool for filtering data in Pivot Tables. With a single click, they make it simple to change values in Pivot Tables and Charts. Slicers come in handy when creating dashboards in Google Sheets. In this article, let us understand how to use Slicer\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"slicer-google-sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/09\/slicer-in-google-sheets7.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":4754,"url":"https:\/\/sheetstips.com\/pivot-table-in-google-sheets\/","url_meta":{"origin":1131,"position":3},"title":"How to use Pivot Tables in Google Sheets | Detailed Guide on Pivot Table","author":"Varsha","date":"January 3, 2024","format":false,"excerpt":"Spreadsheets provide great analysis skills, but they occasionally lack that extra layer of understanding. It's difficult to summarise or draw conclusions from a basic tabular spreadsheet view when there's a large amount of data. Also Read: Cloud Computing Notes The pivot table comes into play. Most Excel expert users rely\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"pivot-table-in-google-sheets2","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/09\/pivot-table-in-google-sheets3-1.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":883,"url":"https:\/\/sheetstips.com\/insert-pivot-table-google-sheets\/","url_meta":{"origin":1131,"position":4},"title":"How to Insert a Pivot Table in Google Sheets? (Create\/Edit\/Customize Pivot Tables)","author":"Varsha","date":"May 17, 2022","format":false,"excerpt":"Spreadsheets are an excellent tool for working with data. They assist you in organizing your data so that you can gain useful information and insight from it. Google Sheets includes the Pivot table feature, which makes data analysis even more powerful. If you are new to the Pivot table, then\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"insert-pivot-table-google-sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/09\/pivot-table-in-google-sheets3.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":908,"url":"https:\/\/sheetstips.com\/group-by-month-pivot-table-google-sheets\/","url_meta":{"origin":1131,"position":5},"title":"How to Group by Month in Pivot Table in Google Sheets (With Examples)","author":"Varsha","date":"May 17, 2022","format":false,"excerpt":"A Pivot Table in Google Sheets is a function for summarising, sorting, reorganizing, grouping, counting, totaling, or averaging data in a table. It allows us to change rows into columns and columns into rows. It lets you organize your data by any field (column) and perform advanced calculations on it.\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"group-by-month-pivot-table-google-sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/09\/group-by-month-pivot-table-google-sheets9.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/1131"}],"collection":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/comments?post=1131"}],"version-history":[{"count":4,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/1131\/revisions"}],"predecessor-version":[{"id":4944,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/1131\/revisions\/4944"}],"wp:attachment":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media?parent=1131"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/categories?post=1131"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/tags?post=1131"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}