Check back later this week, for a new Excel tutorial. Subscribe ASAP to always be updated and never miss a trick.
Check back later this week, for a new Excel tutorial. Subscribe ASAP to always be updated and never miss a trick.
In this post, you will learn how to resize a table by inserting or deleting rows and columns. This will be examined for both Excel for Windows and Excel for the Web.
Adding or deleting table rows and columns is a simple task once you have made an Excel table in your worksheet.
The main way to insert rows and columns to a table is by using the Resize command:
Tip: You can even press Collapse Dialogue to momentarily cover the Resize Table dialogue box, pick the specific worksheet range, and then select Expand dialogue .
Insert a row or column to a table by entering in a cell right beneath the the final row. Otherwise, it would be to the right of the last column. Another way is by pasting data directly into a cell. Alternatively, by adding rows or columns amongst current rows or columns.
To append a row at the base of the table, begin typing in a cell beneath the final table row. The table enlarges to contain the new row. To insert a column to the right of the table, begin writing in a cell beside the last table column.
With the example displayed below for a row, inputting a value in cell A4 grows the table to occupy that cell in the table together with the nearby cell in column B.
For the example indicated below for a column, entering a value in cell C2 extends the table to comprise column C. Effectively, this labels the table column Qtr 3 since Excel deduced a naming pattern from Qtr 1 and Qtr 2.
If your pasted data in a new row has enough or reduced columns than the table, the table extends to take account every cell in the range you pasted. However, if your pasted data includes excessive columns than the table, the surplus columns are omitted from the table. You must use the Resize command to enlarge the table to contain them.
The example presented below for rows depicts that, pasting the values from A10:B12 in the first row underneath the table (row 5) broadens the table to consider the pasted data.
In the example displayed below for columns, pasting the values from C7:C9 in the first column to right of the table (column C) widens the table to embody the pasted data. In effect, this creates a new heading, Qtr 3.
If you’re in the final row, you can choose Table Rows Above or Table Rows Below.
With the example illustrated below for rows, a row will be added above row 3.
For columns, if you have a cell picked in the table’s rightmost column, you can select between adding Table Columns to the Left or Table Columns to the Right.
In the example displayed below for columns, a column will be added to the left of column 1.
Like you can erase duplicates from any highlighted data in Excel, you can conveniently delete duplicates from a table.
Note: Removed duplicates are invariably erased from the worksheet. If you unintentionally delete data that you wanted to retain, you can use Ctrl+Z or press Undo on the Quick Access Toolbar to restore the removed data. You might even want to deploy conditional formats to earmark duplicate values prior to you deleting them. For further information, see Add, change, or clear conditional formats.
You can use a similar procedure for filtering and deleting blank worksheet rows. For additional details about how to filter for blank rows in a worksheet, see Filter data in a range or table.
This post will show you how to format an Excel table for both Windows and Excel for the Web to achieve your ideal presentation of your data.
Excel is jam-packed full of preformatted table styles that you can utilise to effortlessly format an Excel table. However, you can design and implement a custom table style if the pre-existing table styles are unsuitable for your requirements. Even though you can erase solely custom table styles, you can delete any preformatted table style to guarantee that it stops being associated with a table.
Additionally, you can amend the table further by picking Quick Styles options for table features, like Header and Total Rows, First and Last Columns, Banded Rows and Columns, alongside Auto Filtering.
Note: The screenshots in this post were taken in Excel 2016. If you have another version, your view may be slightly different, but unless otherwise stated, the functionality still operates in exactly the same way.
Once you have a data range that is unformatted as a table, Excel will immediately update it to a table after you choose a table style. You can even alter the format for an established table by picking an alternative format.
Note: Deleting a table style does not erase the table. If you want to work with your data out of a table then you can change the table to a regular range. For further details, see Convert an Excel table to a range of data.
There are multiple table style options that can be switched on and off. To set any of these options:
In this post, you will learn how to import a Word table into Excel. It will walk you through the process of how to do this successfully and also covers some crucial points for you to remember which ensure that you retain all the functionality of the Excel data.
Once you have decided to relocate data from a Word table to Excel, you can overcome the drudgery of manually retyping that data. To achieve this, you simply duplicate it from Word directly. After you copy data from a Word table into an Excel worksheet, every Word table cell’s data will transfer into the worksheet. They will be appear in a separate worksheet cell.
When you’ve pasted the data, you might have to tidy it up. This is to ensure that you can make the best use of Excel’s calculation features. For instance, there could be unnecessary additional spacing in cells. Another scenario is that numbers might have been imported as text instead of numeric values that you can calculate. Alternatively, dates are incorrectly displayed. To get assistance with formatting numbers as dates, currency, percentages, etc., read Format numbers. For guidance with formatting your table’s style, see Format an Excel table.
Note: Ensure that the paste area is vacant prior to you pasting the data. Data inside Word table cells will overwrite any current data in worksheet cells within the paste area. If essential, inspect the table first in Word to ascertain its dimensions.
Note: Excel pastes the contents of each Word table cell into one cell. After you paste the data, you can divide the data throughout extra cells in a column (for example, to divide first and last names so that they appear in individual cells) by using the Text to Columns command. For further information, see Distribute the contents of a cell into adjacent columns.
This blog post will explore the aspects of Excel tables and why they are recommended for simplifying the formatting and analysis of a collection of linked data.
To begin with, changing a cell range into an Excel table (formerly known as an Excel list) makes it simpler to organising and examining an array of similar data.
A table can contain these specific elements:
You can make however many tables as you like in a spreadsheet.
To rapidly design an Excel table, follow this procedure:
You can even watch a video on creating a table in Excel.
Excel contains some built-in features that allow you to work efficiently with your table data:
If you have authoring access to a SharePoint site, you can apply it to export an Excel table to a SharePoint list. Effectively, other people can see, modify, and revise the table data within the SharePoint list. You can design a one-way connection to the SharePoint list to enable you to refresh the table data on the worksheet to consider amendments that are set to the data amongst the SharePoint list. For further information, see Export an Excel table to SharePoint.
In this post, you will learn how to shape data in Power Query through a broad array of sources. These range from editing queries, formatting tables, adding columns, hiding rows or columns, and splitting a text column.
Note: Power Query is also known as Get & Transform in Excel 2016. Information given here relates to both. For more details about this, see Get & Transform in Excel 2016.
Moreover, using Power Query equips you with the ability to shape data from several sources. This is done through modifying the query steps to reflect your data analysis specifications.
The resources below will give you a greater insight into how to use Power Query more effectively, depending on your specific needs for data analysis.
Refresh a query to transfer the newest data into a table without generating the query once more.
Extract data from various data sources and integrate it.
This post will teach you various ways of how to edit query step settings (Power Query). These ways comprise the following:
Any time you append a query step in Power Query, it integrates itself into the sequence of steps that comes after the chosen step. Should you insert a step anywhere else apart from the flow’s endpoint then you must confirm that every following step operates correctly.
The illustrated image presents the Query Editor alongside the Query Settings pane on the right side of the window. Query Editor is the location where the production, amendment, and refinement of Power Query queries occur.
There are two approaches to adding a query step to your query.
Note: After you add a query step, an error may arise in subsequent steps. An error will generate if the new step modifies fields, like column names, that are referenced in any of the steps that trail the added step.
You can edit a current step in two ways.
You might reposition a step up or down in the Steps pane. Altering a step’s position in the Steps pane can cause one or several steps to fail. Make sure you verify that every step is functioning properly when you complete the reorder.
To move a step up or down the list of steps in the Steps pane:
Note: The Query Editor only emerges when you load, edit, or create a new query using Power Query. The displayed video depicts the Query Editor window coming into view after editing a query from an Excel workbook. To view the Query Editor excluding loading or modifying a current workbook query, follow this process:
From the Get External Data section in the Power Query ribbon tab, pick From Other Sources > Blank Query. The video beneath presents one way to display the Query Editor.
This post focuses on what the Query Editor is and what you can do with it. There are also links to relevant aspects that are pivotal for applying it.
Note: Power Query is also known as Get & Transform in Excel 2016. Information supplied here concerns both. For further information, read Get & Transform in Excel 2016.
The Query Editor only displays once you load, edit, or produce a new query. The video below presents the Query Editor window showing up after changing a query from an Excel workbook.
Choose From Other Sources > Blank Query. Do this from the Get External Data section in the Power Query ribbon tab.
Using Query Editor offers you a variety of actions to perform:
To show the Query Editor dialogue box, do the following steps:
To connect to a data source, refer to Import data from external data sources.
The query editor comprises these elements:
This post will teach you all about loading queries into an Excel worksheet in Power Query. It spans across all the various actions you need to do in relation to loading queries into an Excel worksheet. These include:
Note: Power Query is called Get & Transform in Excel 2016. Information given here relates to both. To learn more, see Get & Transform in Excel 2016.
Note: For a fast video on how to present Query Editor, refer to the end of this post.
Power Query provides multiple options for transferring queries into your workbook. You format predefined query load settings in the Options dialogue popup.
There are numerous options to load queries into your workbook:
Note: When you select Load To from the Workbook Queries pane, you can only Load to worksheet or Load to Data Model. Other load-to options enable you to tweak how you load a query. To learn about the complete range of load options, see How to fine-tune your load options.
Using Power Query Load-To options, you can:
Note: The steps in this section require Excel 2013.
An Excel Data Model is a relational data source created from several tables within an Excel workbook. Inside Excel, a Data Model is used transparently, offering tabular data used in PivotTables, PivotCharts, and Power View reports.
Using Power Query, data and annotations on the Data Model are retained during the process of adjusting the Load to Worksheet setting of a query. Power Query avoids resetting the query results in both the worksheet and the Data Model when amending either one of the two load settings.
To load a query to the Excel Data Model, simply check the Add the data to the Data Model box. Do this in the Load To popup window. To find out more about how to present the Load To popup window, check the Load queries into your workbook section above.
Follow these steps to confirm your default query load settings:
Note: The Query Editor only emerges once you load, edit, or design a new query using Power Query. The video underneath illustrates the Query Editor window displaying when a query has been edited from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query: From the Get External Data section in the Power Query ribbon tab, pick From Other Sources > Blank Query.
This post will teach you more about how to view and manage workbook queries, specifically for Power Query. There is also a link at the end of this about Power BI administration if you’re interested in learning more about the inner operations of Power BI.
Note: Power Query is synonymous with Get & Transform in Excel 2016. Information given in this post relates to both. To learn more, see Get & Transform in Excel 2016.
The Excel Workbook Queries pane allows you to manage your Power Query queries.
This is particularly useful if you are overloaded with excessive queries in your Excel workbook. It also offers you a convenience instead of having to scan through the workbook sheets to locate the essential query. The Workbook Queries pane even allows you to conduct other actions on the workbook queries like edit, duplicate, reference, merge, append, share, and delete a query.
Listed below is the process for viewing and managing your workbook queries:
a. In the Workbook Queries pane, right-click a query, and then pick the pertinent option from the context menu. With the context menu, you can do any of the following:
b. Aim your mouse at a query name in the Workbook Queries pane then choose the relevant option in the preview fly-out screen. Please note that the Duplicate, Reference, Merge, Append, and Properties options are will appear in a pop-up menu that displays once you select the ellipsis (…). This is beside the SEND TO DATA CATALOGUE option in the preview fly-out screen.
Send to Data Catalogue: Share a query. For more details, see Share Queries. This option is exclusively available provided that you are signed in to Power BI.
Move To Group: Form a new group or relocate a query to a group.
Properties: Change name and description.
The purpose of this blog post is to give you a starting point to how Get & Transform works in Excel. It will explore each of the steps involved in this process sequentially and include other resources you can peruse to aid your knowledge and understanding.
The Get & Transform function in Excel enables you to search for data sources, create connections before proceeding to shape that data (for example, delete a column, edit a data type, or merge tables) in ways that fulfil your requirements. After you’ve shaped your data, you can disseminate your findings or reference your query to produce reports.
Seeing those steps in order, they typically happen like this:
Anytime you connect to data, transform it, or combine it with different data sources, an aspect of Get & Transform, known as Query Editor, records every step. This enables you to amend it in whichever way you prefer. Query Editor even allows you to undo, redo, modify the order, or adjust any step… all so you can shape your view of the connected data in your desired way.
Using Get & Transform, you can formulate basic or complex queries to fulfil your requirements. While you insert steps to a query, Query Editor operates covertly to generate a range of discrete instructions that perform your commands. Those instructions originate in the M Language. Users who relish the control and versatility of data scripting can physically make or alter M Language queries using the Advanced Editor. Query Editor and the Advanced Editor are available in extra detail further in this post.
You can start a new query from the Data tab > Get Data > Select a data source. If you cannot see the Get Data button, then press the New Query button from the Data tab. Below is an example of picking from a database source.
Note: Power Query is also available for former versions of Excel as an add-in, alongside in Power BI. To witness Power Query in action in past versions of Excel, check out Getting Started with Power Query.
With a query, you can connect to one data source, like an Access database. Alternatively, you can connect to several files, databases, OData feeds, or websites. Subsequently, you can compile each of those sources into one integrated database by applying your personalised combinations and discover perspectives only revealed in this product.
Once you choose Get Data from the Get & Transform section on the Data tab (or New Query if the Get Data button is missing from your view), you’ll notice multiple data sources to pick from. These comprise files like Excel workbooks or Text/CSV files, databases including Access, SQL Server, Oracle, and MySQL, and Azure services like HDInsight or Blob Storage. Also, various kinds of other sources like the Web, SharePoint Lists, Hadoop Files, Facebook, Salesforce, and others.
Note: You can find out more about what data sources are available in which Excel versions here: Where is Get & Transform (Power Query).
Once you connect to a data source, Get & Transform presents a Navigator pane. This enables you to amend the data from its source. Once you choose Edit from the Navigator window, Get & Transform displays the Query Editor. This effectively is a tailored window that simplifies and showcases your data connections and the your implemented transformations. The next section, Transform, offers additional information about Query Editor.
Another functionality of Get & Transform is transform which allows you to transform the data from your data sources in meaningful ways to inform your data analysis. Transforming data means altering it in a particular way to achieve your goals – for example, you could delete a column, modify a data type, or merge tables . Each of these qualifies as a data transformation. While you transform data, it holistically adapts to the shape you require to enrich your analysis. The method of assigning transformations to one or multiple datasets is usually known as shaping data.
Excel relies upon a corresponding Query Editor to accelerate and highlight data transformations. Once you choose Data > Get Data, then click the data source, like a workbook, or a database. Following this, the Navigator window emerges allowing you to confirm which table (or tables) you aim to use in your query. After you pick a table, a preview relating to its data is reflected in the right pane of the Navigator window.
If you choose Load, the data source will materialise into Excel as well. If you press the Transform Data option, that will emerge Query Editor.
Query Editor has the following capabilities:
The transformations you apply to your data connections collectively constitute your query.
It’s important to know that the actions you define in Query Editor don’t change the original source data. Instead, Excel records each step you take when connecting or transforming the data, and once you’ve finished shaping the data, it takes a snapshot of the refined data set and brings it into the workbook.
There are many transformations you can apply to data. You can also write your own transformations using the M Language with Query Editor’s Advanced Editor. You can open the Advanced Editor from Query Editor’s Home or View tabs, where you can modify the M Language steps associated with the existing query, or create your own.
When you’re done creating your query, you can select Close & Load from the Home tab. The query results will then appear in Excel and available in a new workbook tab.
Learn more about Transform:
After you save an Excel workbook including a query, the query is saved by default too. You can see every query in an Excel workbook by choosing Queries & Connections on the Data tab.
Any time you right-click directly over a query title in the Queries & Connections pane, you will presented with multiple options. For example, you can Duplicate a query, allowing you to make alterations to part or the entire aspects of a query excluding any effect to the initial query. It’s similar to designing a query template that you can subsequently adjust to form tailored datasets – such as one for retail, another for wholesale, and a different one for inventory, collectively, they are associated with the equivalent data connections.
You can even Merge or Append queries, enabling you to convert queries into recyclable stepping stones.
Additionally, you can publish your workbook to Power BI, and produce online reports that are shareable with your group, automatically updated, and revisable. Click File > Publish > Publish to Power BI to publish a workbook to Power BI.
Note: Before you publish your workobook to Power BI, it must be saved to OneDrive for Business.
Learn more about Managing your queries: