{"id":5092,"date":"2020-03-21T08:47:04","date_gmt":"2020-03-21T08:47:04","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=5092"},"modified":"2024-03-28T14:39:43","modified_gmt":"2024-03-28T14:39:43","slug":"the-data-tab-on-ribbon","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/","title":{"rendered":"The Data Tab on Ribbon in Excel"},"content":{"rendered":"\n<p>You can use the Data tab on the Ribbon when you manipulate\ndata. Options on this tab allow you to import, transform, sort, and split data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Get &amp; Transform Data<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"348\" height=\"91\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png\" alt=\"\" class=\"wp-image-5093\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png 348w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform-300x78.png 300w\" sizes=\"(max-width: 348px) 100vw, 348px\" \/><\/figure>\n\n\n\n<p>In this part of the Data tab, you can load data from\ndifferent file formats, such as:<\/p>\n\n\n\n<ul>\n<li>text files<\/li>\n\n\n\n<li>CSV<\/li>\n\n\n\n<li>XML<\/li>\n\n\n\n<li>JSON<\/li>\n<\/ul>\n\n\n\n<p>You can also get data from the database. Here, you will find\nthe most popular types of databases:<\/p>\n\n\n\n<ul>\n<li>Microsoft Access<\/li>\n\n\n\n<li>MySQL<\/li>\n\n\n\n<li>PostgreSQL<\/li>\n\n\n\n<li>Oracle<\/li>\n<\/ul>\n\n\n\n<p>There are also a few other databases that are less popular\nbut also used in many places.<\/p>\n\n\n\n<p>There are a few other places, you can get data, such as the Microsoft\ncloud called Azure. Click the Get Data Dropdown list to reveal all of them. <\/p>\n\n\n\n<p>You can also load data from a website by choosing <strong>From Web<\/strong> and inserting a URL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Querries &amp; Connections<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"213\" height=\"92\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/querries-connections.png\" alt=\"\" class=\"wp-image-5094\"\/><\/figure>\n\n\n\n<p>With the <strong>Refresh All<\/strong>\nbutton, you can refresh all data coming from the external sources.<\/p>\n\n\n\n<p>Click <strong>Queries &amp;\nConnections<\/strong> to display all connections from the outside sources to your\nworkbook.<\/p>\n\n\n\n<p>The <strong>Properties<\/strong>\noption is enabled, only if you select a cell that is populated with an external\nconnection.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Sort &amp; Filter<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"217\" height=\"92\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/sort-filter.png\" alt=\"\" class=\"wp-image-5095\"\/><\/figure>\n\n\n\n<p>This area is similar to the <strong>Editing<\/strong> area on the <strong>Home<\/strong>\ntab. You have options to sort and filter data.<\/p>\n\n\n\n<p>What you do have here, that is not present on the Home tab,\nis the <strong>Advanced Filter<\/strong> with\nadditional options to filter.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Data Tools<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"446\" height=\"92\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/data-tools.png\" alt=\"\" class=\"wp-image-5096\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/data-tools.png 446w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/data-tools-300x62.png 300w\" sizes=\"(max-width: 446px) 100vw, 446px\" \/><\/figure>\n\n\n\n<p>Here, you can find such an option, as <a href=\"https:\/\/officetuts.net\/excel\/en\/training\/splitting-text-using-text-to-columns-wizard\">Text\nto Columns<\/a>, to split data from one column to multiple, by specifying a\ndelimiter, such as a comma, tab, space, etc.<\/p>\n\n\n\n<p><a href=\"https:\/\/officetuts.net\/excel\/en\/training\/flash-fill\">Flash\nFill<\/a> uses a complicated algorithm to determine what data to place inside\ncells, by analyzing surrounding cells.<\/p>\n\n\n\n<p>The next option, called <a href=\"https:\/\/officetuts.net\/excel\/en\/examples\/duplicates\">Remove Duplicates<\/a>\nkeeps <a href=\"https:\/\/officetuts.net\/excel\/formulas\/excel-list-unique-values\/\">unique values<\/a>.<\/p>\n\n\n\n<p>Under the <a href=\"https:\/\/officetuts.net\/excel\/en\/training\/data-validation\">Data Validation<\/a>\ntab, you can specify the criteria that have to be met for the particular cells.\nYou can choose whether you want decimal values, text, date, etc. You can also\ndetermine ranges for values, or length for text and a few other options.<\/p>\n\n\n\n<p>The <strong>Consolidate<\/strong>\noption merges the same rows and consolidates the data in the chosen way. For\nexample, you can have a list of people and the amount they earned. So the names\nare used multiple times. With this option, you can display the name once, with\nthe summed earnings.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Forecast<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"122\" height=\"92\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/forecast.png\" alt=\"\" class=\"wp-image-5097\"\/><\/figure>\n\n\n\n<p>Here, you have all the options such as Goal Seek, that calculates\nthe value of a single input and should be used when there is one unknown\nvariable.<\/p>\n\n\n\n<p>The <strong>Manage Data Model<\/strong>\noption opens the Power Pivot window. Before you can use this feature, you must\nenable the Data Analysis add-ins.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Outline<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"251\" height=\"92\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/outline.png\" alt=\"\" class=\"wp-image-5098\"\/><\/figure>\n\n\n\n<p>This part of the Data tab is used to <a href=\"https:\/\/officetuts.net\/excel\/en\/examples\/group-data\">group and ungroup\nrows and columns<\/a>. The Subtotal feature lets you group the whole table and\ncalculate rows of related data, by inserting subtotals and grand totals.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You can use the Data tab on the Ribbon when you manipulate data. Options on this tab allow you to import, transform, sort,&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Data Tab on Ribbon in Excel<\/title>\n<meta name=\"description\" content=\"Learn about Excel&#039;s Data tab &amp; its options to import, transform, sort, and split data. Includes guides on connections, filtering, and data tools.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Data Tab on Ribbon in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn about Excel&#039;s Data tab &amp; its options to import, transform, sort, and split data. Includes guides on connections, filtering, and data tools.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-21T08:47:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T14:39:43+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"The Data Tab on Ribbon in Excel\",\"datePublished\":\"2020-03-21T08:47:04+00:00\",\"dateModified\":\"2024-03-28T14:39:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/\"},\"wordCount\":482,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png\",\"keywords\":[\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/\",\"name\":\"The Data Tab on Ribbon in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png\",\"datePublished\":\"2020-03-21T08:47:04+00:00\",\"dateModified\":\"2024-03-28T14:39:43+00:00\",\"description\":\"Learn about Excel's Data tab &amp; its options to import, transform, sort, and split data. Includes guides on connections, filtering, and data tools.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#primaryimage\",\"url\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png\",\"contentUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png\",\"width\":348,\"height\":91},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Data Tab on Ribbon in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Data Tab on Ribbon in Excel","description":"Learn about Excel's Data tab &amp; its options to import, transform, sort, and split data. Includes guides on connections, filtering, and data tools.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/","og_locale":"en_US","og_type":"article","og_title":"The Data Tab on Ribbon in Excel","og_description":"Learn about Excel's Data tab &amp; its options to import, transform, sort, and split data. Includes guides on connections, filtering, and data tools.","og_url":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/","article_published_time":"2020-03-21T08:47:04+00:00","article_modified_time":"2024-03-28T14:39:43+00:00","og_image":[{"url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"The Data Tab on Ribbon in Excel","datePublished":"2020-03-21T08:47:04+00:00","dateModified":"2024-03-28T14:39:43+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/"},"wordCount":482,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png","keywords":["pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/","url":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/","name":"The Data Tab on Ribbon in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png","datePublished":"2020-03-21T08:47:04+00:00","dateModified":"2024-03-28T14:39:43+00:00","description":"Learn about Excel's Data tab &amp; its options to import, transform, sort, and split data. Includes guides on connections, filtering, and data tools.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#primaryimage","url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png","contentUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2020\/03\/get-transform.png","width":348,"height":91},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"The Data Tab on Ribbon in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5092"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=5092"}],"version-history":[{"count":4,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5092\/revisions"}],"predecessor-version":[{"id":17380,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5092\/revisions\/17380"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=5092"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=5092"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=5092"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}