{"id":1712,"date":"2015-10-13T23:59:58","date_gmt":"2015-10-13T21:59:58","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1712"},"modified":"2024-02-06T15:58:08","modified_gmt":"2024-02-06T14:58:08","slug":"consolidate-with-3d-formulas-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/consolidate-with-3d-formulas-in-excel\/","title":{"rendered":"Consolidate with 3D Formulas in Excel"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/other\/3d-formulas\/\" target=\"_blank\" rel=\"noopener\">3D Formulas or References in<\/a> Excel are a great way to consolidate data from multiple sheets.<\/p>\n<p>3D Formulas reference several worksheets that have the same structure which allows you to consolidate by using the SUM function.<\/p>\n<table style=\"height: 231px;\" width=\"979\">\n<tbody>\n<tr>\n<td width=\"167\"><strong><em>Formula breakdown:<\/em><\/strong><\/p>\n<p>SUM(<span style=\"color: #339966;\">Sheet1:Sheet4<\/span><strong><span style=\"color: #ff0000;\">!<\/span><\/strong><span style=\"color: #ff6600;\">A1<\/span>)<\/td>\n<td width=\"147\"><\/td>\n<\/tr>\n<tr>\n<td><strong><em>What it means:<\/em><\/strong><\/p>\n<p>SUM(<span style=\"color: #339966;\">from this Sheet#:up to this Sheet#<\/span><strong><span style=\"color: #ff0000;\">!<\/span><\/strong><span style=\"color: #ff6600;\">return the sum of these cells<\/span>)<\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/vKwABAUWE9Y?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><br \/>\n<span style=\"color: #ff0000;\"><strong>STEP 1: <\/strong><\/span>Make sure you have a SUMMARY <em>Sheet<\/em> and several sheets where you want to sum your data from;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9701\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d1.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"814\" height=\"476\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d1.png 814w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d1-300x175.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d1-768x449.png 768w\" sizes=\"(max-width: 814px) 100vw, 814px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2: <\/strong><\/span>All <em>Sheets<\/em> have to have the same structure&#8230;so the same number of columns, rows and cells;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9702\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d2.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"729\" height=\"468\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d2.png 729w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d2-300x193.png 300w\" sizes=\"(max-width: 729px) 100vw, 729px\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9703\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d3.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"751\" height=\"473\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d3.png 751w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d3-300x189.png 300w\" sizes=\"(max-width: 751px) 100vw, 751px\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9704\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d4.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"743\" height=\"469\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d4.png 743w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d4-300x189.png 300w\" sizes=\"(max-width: 743px) 100vw, 743px\" \/> <img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9705\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d5.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"750\" height=\"470\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d5.png 750w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d5-300x188.png 300w\" sizes=\"(max-width: 750px) 100vw, 750px\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9706\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d6.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"738\" height=\"476\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d6.png 738w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d6-300x193.png 300w\" sizes=\"(max-width: 738px) 100vw, 738px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3: <\/strong><\/span>Enter a SUM <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a> in your SUMMARY <em>Sheet<\/em>, preferably in the top left hand corner;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9707\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d7.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"563\" height=\"197\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d7.png 563w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d7-300x105.png 300w\" sizes=\"(max-width: 563px) 100vw, 563px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4: <\/strong><\/span>With your mouse select the first <em>Sheet<\/em> you want to consolidate;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9708\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d8.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"522\" height=\"50\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d8.png 522w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d8-300x29.png 300w\" sizes=\"(max-width: 522px) 100vw, 522px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5: <\/span> <\/strong>Hold down the SHIFT key;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 6: <\/span> <\/strong>Whilst holding the SHIFT key, select the last <em>Sheet<\/em> you want to consolidate with your mouse key;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9709\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d9.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"524\" height=\"53\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d9.png 524w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d9-300x30.png 300w\" sizes=\"(max-width: 524px) 100vw, 524px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 7: <\/span> <\/strong>In the formula bar, type in the active cell that you are in (from Step 3) after the <span style=\"color: #ff0000;\"><strong>!<\/strong><\/span> and press <em>Enter<\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9710\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d10.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"585\" height=\"250\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d10.png 585w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d10-300x128.png 300w\" sizes=\"(max-width: 585px) 100vw, 585px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 8: <\/strong><\/span> Drag the formula across all your cells.\u00a0 That&#8217;s it!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9711\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d11.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"612\" height=\"213\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d11.png 612w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d11-300x104.png 300w\" sizes=\"(max-width: 612px) 100vw, 612px\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate with 3D Formulas in Excel\"  class=\"aligncenter size-full wp-image-9712\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d12.png\" alt=\"Consolidate with 3D Formulas in Excel\" width=\"618\" height=\"226\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d12.png 618w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3d12-300x110.png 300w\" sizes=\"(max-width: 618px) 100vw, 618px\" \/><\/p>\n<p><strong>Note: <\/strong>You can change the <em>Sheet<\/em> names and this will be reflected in your 3D formula.<\/p>\n<p>You cannot move the positioning of the <em>Sheets<\/em> after you create the 3D formula as this throw out the formula.<\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3D-Formulas.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/3D-Formulas.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">3D-Formulas.xlsx<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>3D Formulas or References in Excel are a great way to consolidate data from multiple sheets. 3D Formulas reference several worksheets that have the same structure which allows you to consolidate by using the SUM function. Formula breakdown: SUM(Sheet1:Sheet4!A1) What it means: SUM(from this Sheet#:up to this Sheet#!return the sum of these cells) \ufeff STEP [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17314,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Consolidate with 3D Formulas in Excel","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[310,3,16],"tags":[43],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1712"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=1712"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1712\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17314"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=1712"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=1712"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=1712"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}