Featured

Excel Basics

Get Started!

The basic elements of Excel use the following functions: SUM, COUNT, AVERAGE, TIME & DATE. With this tutorial, you will be able to perform calculations and keep time records.

Check back later this week, for a new Excel tutorial. Subscribe ASAP to always be updated and never miss a trick.

How to Resize a Table

  • Applies to: Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.

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.

Using the Resize Command

The main way to insert rows and columns to a table is by using the Resize command:

  1. Select any point in the table then the Table Tools option displays.
  2. Press Design > Resize Table.
using the resize table option from the Table Tools
  1. Choose the whole array of cells you prefer your table to contain. Start with the upper-leftmost cell. In the illustrated example underneath, the first table occupies the range A1:C5. When it has been resized to insert three rows and two columns, the table will then span the range A1:E8.
Resize Table

Tip: You can even press Collapse Dialogue Button image to momentarily cover the Resize Table dialogue box, pick the specific worksheet range, and then select Expand dialogue Button image .

  1. Once you’ve chosen your desired range, click OK.

Alternative ways to add rows and columns to resize a table

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.

Begin typing

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.

Adding a row by typing in the first row below a table

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.

Typing a value in a cell to the right of the table adds a column

Paste data   

  • To insert a row by pasting, paste your data in the leftmost cell. This will reside beneath the final table row. To create a column by pasting, paste your data immediately to the right of the table’s rightmost column.

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.

Pasting data below the table expands the table to include it

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.

Pasting column data expands the table and adds a heading

Use Insert to add a row   

  1. To add a row, choose a cell or row other than the header row, and right-click. To insert a column, select any cell in the table and right-click.
  2. Aim at Insert, and click Table Rows Above to append a new row, or Table Columns to the Left to produce a new column.

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.

Insert Table Rows Above

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.

Inserting a table column

Delete table rows or columns to resize a table

  1. Choose one or further table rows or table columns that you prefer to erase. You can even simply pick one or more cells in the table rows or table columns that you seek to delete.
  2. On the Home tab, in the Cells group, press the arrow beside Delete. Then, select Delete Table Rows or Delete Table Columns. The Cells group on the Home tab Additionally, you can right-click one or more rows or columns, move to Delete on the shortcut menu, and then press Table Columns or Table Rows. Alternatively, you can right-click one or more cells in a table row or table column, aim at Delete, and then select Table Rows or Table Columns.

Remove duplicates from a table to resize it

Like you can erase duplicates from any highlighted data in Excel, you can conveniently delete duplicates from a table.

  1. Select anywhere in the table. This presents the Table Tools, creating the Design tab.
  2. On the Design tab, in the Tools group, press Remove Duplicates.Excel Ribbon Image
  3. In the Remove Duplicates dialogue box, below Columns, choose the columns with your target duplicates to remove. You can even select Unselect All and then pick your desired columns or press Select All to choose every single column.

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 Undo button 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.

Delete blank rows from a table to resize it

  1. First of all, check that the active cell lies within a table column.
  2. Select the arrow Filter drop-down arrow in the column header.
  3. To filter for blanks, in the AutoFilter menu at the top of the list of values, deselect (Select All), and then at the bottom of the list of values, click (Blanks). Note: The (Blanks) checkbox is available solely on the condition that the range of cells or table column includes a minimum of one blank cell.
  4. Pick the blank rows in the table, and then press CTRL+- (hyphen).

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.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Other Similar Tables Topics

How can I merge two or more tables?

Create an Excel table in a worksheet

Use structured references in Excel table formulas

Format an Excel table

Other Links

Software Bytes – Official Website

Back to Excel Tutorial

How to Format an Excel table?

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011.

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.

Windows

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.

Example of data formatted as an Excel table
© Microsoft, 2020.

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.

Pick a table style

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.

  1. Choose any cell inside the table, or range of cells you prefer to format as a table.
  2. On the Home tab, press Format as Table.
Excel Style Gallery selections for Format as Table
  1. Select your desired table style to use.

Notes: 

  • Auto Preview – Excel will instantly format your data range or table with a preview of any style you pick, but will merely set that style if you press Enter or click with the mouse to verify it. You can scroll through the table formats with the mouse or your keyboard’s arrow keys.
  • Once you use Format as Table, Excel promptly transforms your data range to a table. Although, you have the choice to revert the table back to a regular range if you prefer not to work with your data among a table. This has the benefit of retaining the table style formatting that you defined. For more details, see Convert an Excel table to a range of data.

Create or delete a custom table style

Important: 

  • When created, custom table styles are accessible from the Table Styles gallery below the Custom section.
  • Custom table styles are exclusively stored in the present workbook, and are unavailable in other workbooks.

Create a custom table style

  1. Choose any cell in your preferred table to use to design a custom style.
  2. On the Home tab, select Format as Table, or expand the Table Styles gallery from the Table Tools > Design tab (the Table tab on a Mac).
  3. Press New Table Style, which will reveal the New Table Style dialogue.New Table Style dialog options for applying custom styles to a table
  4. In the Name box, enter a name for the new table style.
  5. In the Table Element box, do one of the following:
    • To format an element, select the element, then pick Format. Then, choose your desired formatting options from the FontBorder or Fill tabs.
    • To delete current formatting from an element, pick the element, and then select Clear.
  6. Beneath Preview, you can witness how the formatting modifies that you applied amend the table.
  7. To employ the new table style as the standard table style in the existing workbook, click the Set as default table style for this document checkbox.

Delete a custom table style

  1. Choose any cell in your sought table to erase the custom table style.
  2. On the Home tab, select Format as Table, or expand the Table Styles gallery from the Table Tools > Design tab (the Table tab on a Mac).
  3. Below Custom, right-click your target table style to erase, and then choose Delete on the shortcut menu. Note: All tables in the current workbook that are using that table style will be displayed in the default table format.

Remove a table style

  1. Click any cell in the table from which you want to remove the current table style.
  2. On the Home tab, select Format as Table, or expand the Table Styles gallery from the Table Tools > Design tab (the Table tab on a Mac).
  3. Choose Clear. The table will be presented in the basic table 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.

Choose table style options to format the table elements

There are multiple table style options that can be switched on and off. To set any of these options:

  1. Pick any cell in the table.
  2. Navigate to Table Tools > Design, or the Table tab on a Mac. Then, in the Table Style Options group, check or uncheck any of the following:Image of the Table Tools option on the Ribbon when a table cell is selected
    • Header Row – Add or delete formatting from the first row in the table.
    • Total Row – Easily add SUBTOTAL functions such as SUM, AVERAGE, COUNT, MIN/MAX to your table from a drop-down menu. SUBTOTAL functions enable you to encompass or omit hidden rows in calculations.
    • First Column – Set or extract formatting from the first column in the table.
    • Last Column – Transfer or erase formatting from the last column in the table.
    • Banded Rows – Show odd and even rows with alternating shading for ease of reading.
    • Banded Columns – Present odd and even columns with alternating shading for simple reading.
    • Filter Button – Turn AutoFilter on and off.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Other Similar Topics of Tables

A Snapshot of Excel tables

Video: Create an Excel table

Total the data in an Excel table

Resize a table by adding or removing rows and columns

Excel table compatibility issues

Export an Excel table to SharePoint

Convert an Excel table to a range

Filter data in a range or table

Import a Word table into Excel

  • Applies to: Excel for Microsoft 365, Word for Microsoft 365, Excel 2019, Word 2019, Excel 2016, Word 2016, Excel 2013, Word 2013, Excel 2010, Word 2010, Excel 2007, Word 2007.

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.

Important Note: 

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.

How to import a Word table into Excel

  1. Firstly, open a Word document. Next, choose the specific rows and columns of the table that you prefer to copy into an Excel worksheet. Check that there aren’t any extra carriage returns within the table’s cells, otherwise this could result in excessive rows in Excel. 
A highlighted selection of some columns and rows in a Word table that are to be copied into Excel.
  1. Press CTRL+C to copy the range.
  2. In the Excel worksheet, click the upper-left corner of your target worksheet area for pasting the Word table.
An Excel worksheet.

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.

  1. Press CTRL+V.
  2. To edit the formatting, choose Paste Options Button image beside your pasted data, and then do the following:
    • To apply the formatting that is formatted to the worksheet cells, select Match Destination Formatting.
    • For deploying the formatting of the Word table, pick Keep Source Formatting.

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.

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

A Snapshot of Excel Tables

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac.

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.

Example of data formatted as an Excel table
© Microsoft, 2020.

Important Notes about Excel Tables: 

  • Excel tables must not be mistaken for the data tables that are affiliated with a group of what-if analysis commands. For further details about data tables, read Calculate multiple results with a data table.
  • Another vital note is that you are unable to design or add tables in a shared workbook.

Learn about the elements of Excel tables

A table can contain these specific elements:

  • Header row – Automatically, a table includes a header row. Each table column by default has filtering activated in the header row to ensure that you can filter or sort your table data swiftly. For further information, see Filter data or Sort data.
Sort or apply a filter to the table
© Microsoft, 2020.

  • Banded rows – Alternate shading or banding in rows aids with clearer distinctions amongst the data.
Excel table with header data, but not selected with the My table has headers option, so Excel added default header names like Column1, Column2.
© Microsoft, 2020.
  • Calculated columns – By typing a formula in one cell in a table column, you can generate a calculated column. This will cause that formula to be imminently relayed to each of the remaining cells in that table column. For further details, see Use calculated columns in an Excel table.
Add a single formula in a table cell that will autocomplete to create a calculated column
© Microsoft, 2020.
  • Total row – After you insert a total row to a table, Excel offers you an AutoSum drop-down list to pick from functions including SUM, AVERAGE, and so forth. Once you choose one of these options, the table will instantly transform them to a SUBTOTAL function. In effect, it will skip rows that have been concealed with an automatic filter. If you prefer to take account of hidden rows in your calculations, however, you can alter the SUBTOTAL function arguments. To learn more about how to do this, also see Total the data in an Excel table.
An Excel table with revenue audit data for a small business.
© Microsoft, 2020.
  • Sizing handle – A sizing handle by the lower-right corner of the table enables you to drag the table to your ideal size.
Drag the resize handle to resize your table
© Microsoft, 2020.

Produce an Excel table

You can make however many tables as you like in a spreadsheet.

To rapidly design an Excel table, follow this procedure:

  1. Choose the cell or the range within the data.
  2. Pick Home > Format as Table.
  3. Confirm a table style.
  4. In the Format as Table dialogue box, click the checkbox beside My table as headers. Only select this checkbox if you are seeking the initial row of the range to occupy the header row. After this, press OK.

You can even watch a video on creating a table in Excel.

Working productively with your Excel table data

Excel contains some built-in features that allow you to work efficiently with your table data:

  • Using structured references – Rather than using cell references, like A1 and R1C1, you can employ structured references that reference table names inside a formula. For further details, check Using structured references with Excel tables.
  • Ensuring data integrity – You can use the pre-existing data validation feature in Excel. For instance, you might decide to solely permit numbers or dates in a column of a table. To learn more about how to guarantee data integrity, see Apply data validation to cells.

Export an Excel table to a SharePoint site

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.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Other Similar Topics of Tables (Excel Tables)

Format an Excel table

Excel table compatibility issues

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

How to Shape Data (Power Query)?

  • Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010.

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.

Getting Started with learning how to shape data (Power Query).
© Microsoft, 2020.

Resources to learn about shaping data

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.

Introduction to Microsoft Power Query for Excel

  • Firstly, with the Query Editor, you can sift through, define, and conduct data-transform operations across a data source.

How-to : Import data from external data sources

  • Additionally, Microsoft Power Query for Excel enables you to import data into Excel from a multiple range of data sources. You can also use the Query Editor to shape data by amending query steps.

Shape or transform a query

  • Shape data from numerous data sources by adding, erasing or changing query steps. When doing this, it is crucial that they correspond to your data analysis requirements.

Refresh a query

Refresh a query to transfer the newest data into a table without generating the query once more.

Combine data from multiple data sources

Extract data from various data sources and integrate it.

Filter a table

  • Filter a table to shrink the size of query results by omitting rows or columns according to on size, value, or condition.

Sort a table

  • Sort table rows in your query results ranked by a criteria. For example, like the alphabetical or numerical value of one or several columns. Although, it could also be executed by ascending or descending order.

Group rows in a table

  • Group the values from different rows into a single value. More importantly, this will be dependent on the values in one or more columns.

Expand a column containing an associated table

  • Expand a column with an associated table to present the related data, then extract some or all column values from that table.

Aggregate data from a column

  • Aggregate data from any column including an associated table to showcase the results of a group operation. This could comprise Sum, Count, Average, Min, and Max.

Insert a custom column into a table

  • Add an Index or Custom column to your current query.

Edit query step settings

  • With the Steps pane, add, modify, rearrange, or remove query steps to adjust how data is transformed.

Combine multiple queries

  • Combine numerous queries, by merging or appending them. The Merge and Append operations are undertaken on any query with a tabular shape. This happens with no consideration of the data’s source.

Merge columns

  • Merge values in two or more columns in a query.

Remove columns

  • Delete selected columns or Remove Other Columns from a query.

Remove rows with errors

  • Erase rows from a query that displays data errors.

Promote a row to column headers

  • Upgrade a row to be a column-heading row.

Split a column of text

  • Divide a column of text into a series of columns. This can be done either by delimiter or by a number of characters.

Insert a query to the worksheet

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

How to Edit Query Step Settings (Power Query)?

  • Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010.
  • Note: Power Query is also known as Get & Transform in Excel 2016. Information stated here corresponds to both. To learn more, see Get & Transform in Excel 2016.
  • Note: For a short video on how to present Query Editor, refer to the end of this post.
  • Note:  Once you import or erase an intermediate step in a query, you may run the risk of corrupting a query. Power Query will indicate an Insert Step warning after you attempt to add a new step.

This post will teach you various ways of how to edit query step settings (Power Query). These ways comprise the following:

Add a query step

Use Query Settings

Use a formula

Edit a step

Use Query Settings

Use the formula bar

Delete a step

Reorder a step

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.

Getting Started with Query Settings for learning how to edit query step settings in Power Query.
© Microsoft, 2020.

Add a query step

There are two approaches to adding a query step to your query.

Use Query Settings

  1. The first way is by selecting a query step. Do this in the Query Settings pane, within the APPLIED STEPS list.
  2. Secondly, once you finish a query step that reshapes your data, a query step is inserted underneath the chosen query step. For further information about how reshape your data, see Shape data.

Use a formula

  1. If essential, click a query step in the Steps pane. The query step is placed below the specified query step.
  2. Select the formula icon ( Formula ) to the left of the formula bar.
    A new formula appears in the form = <nameOfTheStepToReference>
  3. Enter the new formula as = Class.Function(ReferenceStep[,otherparameters]).
    For instance: You have a table with the column Gender and you wish to add a column with the value “Ms.” or “Mr.”, depending on the person’s gender. The formula would be = Table.AddColumn(<ReferencedStep>, “Prefix”, each if [Gender] = “F” then “Ms.” else “Mr.”)
Hidden columns

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.

Top of Page

Edit a step

You can edit a current step in two ways.

Use Query Settings

  1. In the APPLIED STEPS list, confirm your desired step you seek to adjust.
  2. Press the gear ( Settings icon ) icon or right-click, then click Edit Settings.
  3. Next, amend the query step values. This will be done in the dialogue box for the particular step.
  4. Finally, select OK.

Use the formula bar

  1. Firstly, choose your preferred step to edit, in the APPLIED STEPS list.
  2. Secondly, edit the formula values as necessary in the formula bar. To find out more about Power Query formulas and how to edit query step settings, read Learn about Power Query Formulas.
  3. Lastly, select Refresh in the Query Editor dialogue.

Top of Page

Delete a step

  1. Choose the ( Delete a step ) icon to the left of your target step to be erased or;
  2. Alternatively, right-click the step, then press Delete or Delete Until End.

Top of Page

Reorder a step

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:

  1. Firstly, right-click your desired step to rearrange in the Steps pane.
  2. After this, select either Move Up or Move Down to move the chosen step one position up or down the list. Do this from the context menu.
    Note: To move a step numerous positions, repeat these steps. Once a step is at the top or bottom of the list, one of the move options is greyed out.

Top of Page

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.

How to see Query Editor in Excel for editing query step settings in Power Query.

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Getting to know the Query Editor

  • Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010.

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.

To view the Query Editor without loading or editing an existing workbook query:

Choose From Other Sources > Blank Query. Do this from the Get External Data section in the Power Query ribbon tab.

How to see Query Editor in Excel

Using Query Editor offers you a variety of actions to perform:

  • You can navigate;
  • Define; and
  • Conduct data transform commands over a data source.

To show the Query Editor dialogue box, do the following steps:

  1. Connect to a data source.
  2. Then, press Edit Query in the Navigator pane. Alternatively, double-click a query in the Workbook Queries pane.

To connect to a data source, refer to Import data from external data sources.

The query editor comprises these elements:

  1. Query Editor ribbon.
  2. Navigator pane – allows you to browse structured data sources. This helps you to locate your desired data table that you seek to query.
  3. Context menus will adapt to an element inside the editor preview grid—like a table column.
  4. Preview grid that illustrates a preview of data from the results of each query step. You engage amongst the Preview pane to shape data and reformat tables into a subject table to reflect your data analysis requirements. For further clarity, see Shape data.
  5. The Query Settings panel, which includes each query step. A step links to each respective data acquisition or data transformation task that can be sent to a query. For greater details about how to edit query steps, see Edit query step properties.
Query Editor Parts

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

How to load queries into an Excel worksheet (Power Query)

  • Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010.

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:

  • Loading queries into your workbook;
  • Fine-tuning your load options;
  • Loading a query to the Excel Data Model; and
  • Setting default query load settings.

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.

I need to:

Load queries into your workbook

Fine-tune my load options

Load a query to the Excel Data Model

Set default query load settings

Loading queries into your workbook

There are numerous options to load queries into your workbook:

  1. Via Search results:
Using Search results for loading queries into your workbook
  1. With the Navigator pane:
Using the Navigator for loading queries into an Excel worksheet
  1. From the Query Editor:Query Editor
  2. From the Workbook Queries pane and the Query context menu:
Using Workbook Queries for loading queries into an Excel worksheet

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.

Fine-tune your load options for loading queries to an Excel worksheet

Using Power Query Load-To options, you can:

  • Choose how you want to see your data.
  • Decide where the data is loaded.
  • Insert data to the data model.

Load a query to the Excel Data Model

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.

Loading queries into the Excel Data Model

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.

Set default query load settings for loading queries into an Excel worksheet

Follow these steps to confirm your default query load settings:

  1. In the Power Query ribbon tab, press Options.
  2. In the Options popup window, pick a Default Query Load Setting.

Options

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.

How to see Query Editor in Excel

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

How to View and Manage Workbook Queries (Power Query)

  • Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010.

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.

Getting Started

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.

The process for how to view and manage your workbook queries

Listed below is the process for viewing and managing your workbook queries:

  1. Access the Excel workbook including your queries.
  2. Sign in to Power BI on the condition that you seek to share any of your queries in the workbook. Conversely, ignore this step, and proceed to the following step. To sign in to Power BI, select Sign In on the POWER QUERY tab. In the Sign In dialogue box, press Sign In, and then type your organisational account credentials that was confirmed for signing up for Power BI.
  3. Choose Workbook in the Manage Queries section of the POWER QUERY tab.
  4. The Workbook Queries pane presents every single query in the workbook. The queries are arranged by the date and time when they were shared or last edited. The most recent one appears at the top of the list.
  5. Moreover, select a query name to view the data referenced by the appropriate query in the workbook.
  6. You can run several actions on a query in the Workbook Queries pane in either of the following two ways:

Workbook Queries Pane – view and manage you 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:

Workbook Queries Context

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 DuplicateReferenceMergeAppend, 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.Manage workbook queries

Actions you can perform on your workbook queries

  1. You can perform the following actions on your workbook queries:
  • Edit: Modify the query in the Query Editor.
  • Duplicate: Generates a point-in-time copy of the query. Automatically, the new query’s name is the first query name adjoins with an integer value in the parenthesis. This increases by one pre-emptively every time you duplicate the initial query. For instance, if you duplicate Query1, a new query with the same query steps will generate with the name Query1 (2). You can amend the title of the new query to easily identify it. Following changes to the original query will have no direct impact on the new query.
  • Reference: Creates a new query that references the output of the original query. The new query follows a similar naming convention as query duplication. You can change the name of the new query to identify it easily. Subsequent changes to the original query will affect the new query if the output is changed.
  • Delete: Erase a query.
  • Merge and Append: Merge and append columns in a query with corresponding columns in different queries in the workbook. For further information about merging and appending queries in Power Query, read Combine multiple queries.

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.

Related Topics

What is Power BI administration?

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

An Introduction to Get & Transform in Excel

  • Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016.

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.

Connecting to and transforming data in Excel in 4 steps: 1 - Connect, 2 - Transform, 3 - Combine, and 4 - Manage.

Get & Transform Steps

Seeing those steps in order, they typically happen like this:

  • Connect – make connections to data residing in the cloud, in a service, or locally.
  • Transform – shape the data to match your needs; the initial source stays intact.
  • Combine – design a data model from numerous data sources, and obtain a unique perspective into the data.
  • Manage – when your query is complete, you can save it, copy it, or relay it for reports.

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 EditorQuery 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.

Get & Transform From Database options

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.

Connect

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 AccessSQL ServerOracle, and MySQL, and Azure services like HDInsight or Blob Storage. Also, various kinds of other sources like the WebSharePoint ListsHadoop FilesFacebookSalesforce, 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.

Transform

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.

Get & Transform > Power Query Navigation pane

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.

Power Query Editor pane in Get & Transform

More about Query Editor

Query Editor has the following capabilities:

  • Logs all your interactions with the data. 
  • Can track and entitle each transformation, or step, you import to the data.
  • Monitors every action in the Applied Steps section of the Query Settings pane. This happens regardless of whether the transformation is a connection to a data source, erasing a column, a merge, or a data type modification.

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.

Power Query Advanced Editor pane with M language editing

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:

Manage

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.

Power Query Queries and Connections pane - Manage section of Get & Transform

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.

Queries & Connections right-click menu options

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:

Need more help with Get & Transform?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial