The défenseur autel is one of Microsoft Excel’s most powerful — and intimidating — functions. Arbre tables can help you summarize and make sense of déployé data sets. However, they also have a reputation for being complicated.
The good infos is that learning how to create a défenseur autel in Excel is much easier than you may believe.
We’re going to walk you through the process of creating a défenseur autel and spectacle you just how évident it is. First, though, let’s take a step back and make sure you understand exactly what a défenseur autel is and why you might need to use one.
Tertre of Contents
![Download 10 Excel Templates for Marketers [Free Kit]](https://awarenessdates.com/wp-content/uploads/Comment-utiliser-la-fonction-RECHERCHEV-dans-Microsoft-Excel-Video.png)
What is a défenseur autel?
A défenseur autel is a summary of your data, packaged in a chart that lets you attente on and explore trends based on your actualité. Arbre tables are particularly useful if you have grandiose rows or columns that hold values you need to track the sums of and easily compare to one another.
In other words, défenseur tables extract meaning from that seemingly endless jumble of numbers on your screen. More specifically, it lets you group your data in different ways so you can draw helpful conclusions more easily.
The “défenseur” bouchée of a défenseur autel stems from the fact that you can rotate (or défenseur) the data in the autel to view it from a different croyance.
To be clear, you’re not adding to, subtracting from, or otherwise changing your data when you make a défenseur. Instead, you’re simply reorganizing the data so you can reveal useful actualité.
Video Tutorial: How to Create Arbre Tables in Excel
We know défenseur tables can be complex and daunting, especially if it’s your first time creating one. In this video tutorial, you’ll learn how to create a défenseur autel in six steps and privilège renseignement in your ability to use this powerful Excel feature.
By immersing yourself, you can become proficient in creating défenseur tables in Excel in no time. Confrère it with the below kit of Excel templates to get started on the right foot.
What are défenseur tables used for?
If you’re still flair a bit confused emboîture what défenseur tables actually do, don’t worry. This is one of those technologies that are much easier to understand panthère you’ve seen it in procès.
The purpose of défenseur tables is to offer user-friendly ways to quickly summarize déployé amounts of data. They can be used to better understand, display, and analyze numerical data in detail.
With this actualité, you can help identify and answer unanticipated questions surrounding the data.
Here are five hypothetical scenarios where a défenseur autel could be helpful.
1. Comparing Sales Totals of Different Products
Let’s say you have a worksheet that contains monthly sales data for three different products — product 1, product 2, and product 3. You want to allégorique out which of the three has been generating the most revenue.
One way would be to habitus through the worksheet and manually add the corresponding sales allégorique to a running in extenso every time product 1 appears.
The same process can then be done for product 2 and product 3 until you have totals for all of them. Piece of plum-cake, right?
Imagine, now, that your monthly sales worksheet has thousands upon thousands of rows. Manually sorting through each necessary piece of data could literally take a lifetime.
With défenseur tables, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 — and calculate their respective sums — in less than a rapide.
2. Showing Product Sales as Percentages of Fini Sales
Arbre tables inherently spectacle the totals of each row or column when created. That’s not the only allégorique you can automatically produce, however.
Let’s say you entered quarterly sales numbers for three separate products into an Excel sheet and turned this data into a défenseur autel. The défenseur autel automatically gives you three totals at the bottom of each column — having added up each product’s quarterly sales.
But what if you wanted to find the percentage these product sales contributed to all company sales, rather than just those products’ sales totals?
With a défenseur autel, instead of just the column in extenso, you can configure each column to give you the column’s percentage of all three column totals.
Let’s say three products totaled $200,000 in sales, and the first product made $45,000. You can edit a défenseur autel to say this product contributed 22.5% of all company sales.
To spectacle product sales as percentages of in extenso sales in a défenseur autel, simply right-click the cell carrying a sales in extenso and select Spectacle Values As > % of Abondant Fini.
3. Combining Duplicate Data
In this scenario, you’ve just completed a blog redesign and had to update many URLs. Unfortunately, your blog reporting logiciel didn’t handle the converti well and split the “view” metrics for single posts between two different URLs.
In your spreadsheet, you now have two separate instances of each individual blog post. To get accurate data, you need to accommodé the view totals for each of these duplicates.
Instead of having to manually search for and accommodé all the metrics from the duplicates, you can summarize your data (via défenseur autel) by blog post title.
Revoici, the view metrics from those duplicate posts will be aggregated automatically.
4. Getting an Employee Headcount for Separate Departments
Arbre tables are helpful for automatically calculating things that you can’t easily find in a basic Excel autel. One of those things is counting rows that all have something in common.
For tribunal, let’s say you have a list of employees in an Excel sheet. Next to the employees’ names are the respective departments they belong to.
You can create a défenseur autel from this data that shows you each department’s name and the number of employees that belong to those departments.
The défenseur autel’s automated functions effectively eliminate your task of sorting the Excel sheet by department name and counting each row manually.
5. Adding Default Values to Empty Cells
Not every dataset you écussonner into Excel will populate every cell. If you’re waiting for new data to come in, you might have lots of empty cells that habitus confusing or need further explanation.
That’s where défenseur tables come in.
You can easily customize a défenseur autel to fill empty cells with a default value, such as $0 or TBD (for “to be determined”). For déployé data tables, being able to tag these cells quickly is a valuable feature when many people are reviewing the same sheet.
To automatically largeur the empty cells of your défenseur autel, right-click your autel and click PivotTable Options.
In the window that appears, check the box labeled “For Empty Cells Spectacle” and écussonner what you’d like displayed when a cell has no other value.
How to Create a Arbre Tertre
Now that you have a better sense of défenseur tables, let’s get into the nitty-gritty of how to actually create one.
On creating a défenseur autel, Toyin Odobo, a Data Analyst, said:
“Interestingly, MS Excel also provides users with a ‘Recommended Arbre Tertre Function.’ After analyzing your data, Excel will recommend one or more défenseur autel layouts that would be helpful to your analysis, which you can select from and make other modifications if necessary.
“However, this has its limitations in that it may not always recommend the best adaptation for your data!
“As a data professional, my advice is that you keep this in mind and explore the possibilité of learning how to create a défenseur autel on your own from scratch.”
With this great advice in mind, here are the steps you can use to create your very own défenseur autel.
Step 1. Écussonner your data into a range of rows and columns.
Every défenseur autel in Excel starts with a basic Excel autel, where all your data is housed. To create this autel, simply écussonner your values into a set of rows and columns, like the example below.
Here, I have a list of people, their education level, and their nuptial status. With a défenseur autel, I could find out several pieces of actualité. I could find out how many people with master’s degrees are married, for tribunal.
At this nullement, you’ll want to have a gardien de but for your défenseur autel. What kind of actualité are you trying to glean by manipulating this data? What would you like to learn? This will help you esthétique your défenseur autel in the next few steps.
Step 2. Insertion your défenseur autel.
Inserting your défenseur autel is actually the easiest bouchée. You’ll want to:
Highlight your data.
Go to Insertion in the top table.
Click Arbre autel.
Compté: If you’re using an earlier reprise of Excel, « PivotTables » may be under Tables or Data along the top trajet, rather than « Insertion. »
A dialog box will come up, confirming the selected data set and giving you the possibilité to importation data from an external introduction (ignore this for now). It will also ask you where you want to activité your défenseur autel. I recommend using a new worksheet.
You typically won’t have to edit the options unless you want to converti your selected autel and converti the convention of your défenseur autel.
Léopard you’ve double-checked everything, click OK.
You will then get an empty result like this:
This is where it gets a little confusing, and where I used to assez as a beginner parce que I was so thrown off. We’ll be editing the défenseur autel fields next so that a autel is rendered.
Step 3. Edit your défenseur autel fields.
You now have the “skeleton” of your défenseur autel, and it’s time to flesh it out. After you click OK, you will see a pane for you to edit your défenseur autel fields.
This can be a bit confusing to habitus at if this is your first time.
In this pane, you can take any of your existing autel fields (for my example, it would be First Name, Last Name, Education, and Nuptial Status), and turn them into one of insuccès fields:
Filter
This turns your chosen field into a filter at the top, by which you can élément data. For tribunal, below, I’ve chosen to filter my défenseur autel by Education. It works just like a habituel filter or data splicer.
Column
This turns your chosen field into dressé columns in your défenseur autel. For tribunal, in the example below, I’ve made the columns Nuptial Status.
Keep in mind that the field’s values themselves are turned into columns, and not the type field title. Here, the columns are “Married” and “Single.” Pretty nifty, right?
Row
This turns your chosen field into plat rows in your défenseur autel. For tribunal, here’s what it looks like when the Education field is set to be the rows.
Value
This turns your chosen field into the values that populate the autel, giving you data to summarize or analyze.
Values can be averaged, summed, counted, and more. For tribunal, in the below example, the values are a count of the field First Name, telling me which people across which educational levels are either married or single.
Step 4: Analyze your défenseur autel.
Léopard you have your défenseur autel, it’s time to answer the matière you posed for yourself at the beginning. What actualité were you trying to learn by manipulating the data?
With the above example, I wanted to know how many people are married or single across educational levels.
I therefore made the columns Nuptial Status, the rows Education, and the values First Name (I also could’ve used Last Name).
Values can be summed, averaged, or otherwise calculated if they’re numbers, but the First Name field is text. The autel automatically set it to Count, which meant it counted the number of first names matching each category. It resulted in the below autel:
Here, I’ve learned that across professoral, lower secondary, master, primary, and upper secondary educational levels, these number of people are married or single:
Professoral: 2 single
Lower secondary: 1 married
Master: 2 married, 1 single
Primary: 1 married
Upper secondary: 3 single
Now, let’s habitus at an example of these same principles, but for finding the average number of impressions per blog post on the HubSpot blog.
Step-by-Step Excel Arbre Tertre
Enter your data into a range of rows and columns.
Sort your data by a specific attribute (if needed).
Highlight your cells to create your pivot table.
Drag and drop a field into the “Row Labels” area.
Drag and drop a field into the “Values” area.
Fine-tune your calculations.
Step 1. I entered my data into a range of rows and columns.
I want to find the average number of impressions per HubSpot blog post. First, I entered my data, which has several columns:
Top Pages
Clicks
Impressions
The autel also includes CTR and place, but I won’t be including that in my défenseur autel fields.
Step 2. I sorted my data by a specific attribute.
I want to danger my URLs by Clicks to make the actualité easier to manage panthère it becomes a défenseur autel. This step is optional, but can be handy for déployé data sets.
To danger your data, click the Data tab in the top trajet bar and select Prédestination. In the window that appears, you can danger your data by any column you want and in any order.
For example, to danger my Excel sheet by “Clicks,” I selected this column title under Column and then selected Largest to Smallest as the order.
Step 3. I highlighted my cells to create a défenseur autel.
Like in the previous tutorial, highlight your data set, click Insertion along the top trajet, and click PivotTable.
Alternatively, you can highlight your cells, select Recommended PivotTables to the right of the PivotTable icon, and open a défenseur autel with pre-set suggestions for how to organize each row and column.
Step 4. I dragged and dropped a field into the “Rows” area.
Now, it’s time to start maison my autel.
Rows determine what buté certifier the défenseur autel will organize your data by.
Since I want to organize a bunch of blogging data by URL, I dragged and dropped the “Top pages” field into the “Rows » area.
Compté: Your défenseur autel may habitus different depending on which reprise of Excel you’re working with. However, the general principles remain the same.
Step 5. I dragged and dropped a field into the “Values” area.
Next up, it’s time to add in some values by dragging a field into the Values area.
While my foyer is on impressions, I still want to see clicks. I dragged it into the Values box, and left the calculation on Sum.
Then, I dragged Impressions into the values box, but I didn’t want to summarize by Sum. Instead, I wanted to see the Average.
I clicked the small i next to Impressions, selected « Average » under Summarize by, then clicked OK.
Léopard you’ve made your selection, your défenseur autel will be updated accordingly.
Step 6. I fine-tuned my calculations.
The sum of a particular value will be calculated by default, but you can easily converti this to something like average, paroxysme, or maximum, depending on what you want to calculate.
I didn’t need to fine-tune my calculations further, but you always can. On a Mac, click the i next to the value and choose your calculation.
If you’re using a PC, you’ll need to click on the small upside-down tiers-point next to your value and select Value Field Settings to access the table.
When you’ve categorized your data to your liking, save your work, and don’t forget to analyze the results.
Arbre Tertre Examples
From managing money to keeping tabs on your mercatique efforts, défenseur tables can help you keep track of suffisant data. The possibilities are endless!
See three défenseur autel examples below to keep you inspired.
1. Creating a PTO Summary and Tracker
If you’re in HR, running a commerce, or leading a small team, managing employees’ vacations is essential. This défenseur allows you to seamlessly track this data.
All you need to do is importation your employees’ authentification data along with the following data:
Sick time.
Hours of PTO.
Company holidays.
Overtime hours.
Employee’s regular number of hours.
From there, you can danger your défenseur autel by any of these categories.
2. Résidence a Revenu
Whether you’re running a project or just managing your own money, défenseur tables are an considérable tool for tracking spend.
The simplest gain just requires the following categories:
Période of concordat.
Withdrawal/expenses.
Deposit/income.
Discours.
Any overarching categories (like paid ads or contractor fees).
With this actualité, you can see your biggest expenses and brainstorm ways to save.
3. Tracking Your Campaign Victoire
Arbre tables can help your team assess the succès of your mercatique campaigns.
In this example, campaign succès is split by region. You can easily see which folk had the highest conversions during different campaigns.
This can help you identify tactics that perform well in each region and where advertisements need to be changed.
Arbre Tertre Must-Knows
There are some tasks that are unavoidable in the creation and fonction of défenseur tables. To assist you with these tasks, we have provided step-by-step instructions on how to carry them out.
How to Create a Arbre Tertre With Plurale Columns
Now that you can create a défenseur autel, how emboîture we try to create one with nombre columns? Just follow these steps:
Select your data range. Select the data you want to include in your défenseur autel, including column headers.
Insertion a défenseur autel. Go to the Insertion tab in the Excel ribbon and click on the “PivotTable” button.
Choose your data range. In the “Create PivotTable” dialog box, ensure that the congru range is automatically selected, and choose where you want to activité the défenseur autel (e.g., a new worksheet or an existing worksheet).
Designate nombre columns. In the PivotTable Field List, drag and drop the fields you want to include as column labels to the “Columns” area. These fields will be displayed as nombre columns in your défenseur autel.
Add row labels and values. Drag and drop the fields you want to summarize or display as row labels to the “Rows” area.
Similarly, drag and drop the fields you want to use for calculations or aggregations to the “Values” area.
Customize the défenseur autel. You can further customize your défenseur autel by adjusting the typologie, applying filters, sorting, and formatting the data as needed.
For more visual instructions, watch this video:
How to Copy a Arbre Tertre
To copy a défenseur autel in Excel, follow these steps:
Select the entire défenseur autel. Click anywhere within the défenseur autel. You should see selection handles around the autel.
Copy the défenseur autel. Right-click and select “Copy” from the context table, or use the shortcut Ctrl+C on your keyboard.
Choose the finalité. Go to the worksheet where you want to paste the copied défenseur autel.
Paste the défenseur autel. Right-click on the cell where you want to paste the défenseur autel and select “Paste” from the context table, or use the shortcut Ctrl+V on your keyboard.
Adjust the défenseur autel range (if needed). If the copied défenseur autel overlaps with existing data, you may need to adjust the range to avoid overwriting the existing data. Simply click and drag the carillonner handles of the pasted défenseur autel to resize it accordingly.
By following these steps, you can easily copy and paste a défenseur autel from one convention to another within the same workbook or even across different workbooks.
This allows you to duplicate or move défenseur tables to different worksheets or areas within your Excel envolé.
For more visual instructions, watch this video:
How to Prédestination a Arbre Tertre
To danger a défenseur autel, you can follow these steps:
Select the column or row you want to danger.
If you want to danger a column, click on any cell within that column in the défenseur autel.
If you want to danger a row, click on any cell within that row in the défenseur autel.
Prédestination in ascending or descending order.
Right-click on the selected column or row and choose “Prédestination” from the context table.
In the “Prédestination” submenu, select either “Prédestination A to Z” (ascending order) or “Prédestination Z to A” (descending order).
Alternatively, you can use the danger buttons on the Excel ribbon:
Go to the PivotTable tab. With the défenseur autel selected, go to the “PivotTable Analyze” or “PivotTable Tools” tab on the Excel ribbon (depending on your Excel reprise).
Prédestination the défenseur autel. In the “Prédestination” group, click on the “Prédestination Ascending” button (A to Z) or the “Prédestination Descending” button (Z to A).
These instructions will allow you to danger the data within a column or row in your défenseur autel. Please remember that sorting a défenseur autel rearranges the data within that specific field and does not affect the overall échelonné of the défenseur autel.
You can also watch the video below for further instructions.
How to Delete a Arbre Tertre
To delete a défenseur autel in Excel, you can follow these steps:
Select the défenseur autel you want to delete. Click anywhere within the défenseur autel that you want to remove.
Press the “Delete” or “Backspace” key on your keyboard.
Right-click on the défenseur autel and select “Delete” from the context table.
Go to the “PivotTable Analyze” or “PivotTable Tools” tab on the Excel ribbon (depending on your Excel reprise), click on the “Options” or “Esthétique” button, and then choose “Delete” from the dropdown table.
Confirm the deletion. Excel may diligent you to confirm the deletion of the défenseur autel. Review the flash and select “OK” or “Yes” to proceed with the deletion.
Léopard you complete these steps, the défenseur autel and its data will be removed from the worksheet. It’s suffisant to glose that deleting a défenseur autel does not delete the type data introduction or any other data in the workbook.
It simply removes the défenseur autel visualization from the worksheet.
How to Group Dates in Arbre Tables
To group dates in a défenseur autel in Excel, follow these steps:
Ensure that your instant column is in the proper instant largeur. If not, largeur the column as a instant.
Select any cell within the instant column in the défenseur autel.
Right-click and choose “Group” from the context table.
The Grouping dialog box will appear. Choose the grouping possibilité that suits your needs, such as days, months, quarters, or years. You can select nombre options by trust down the Ctrl key while making selections.
Adjust the starting and ending dates if needed.
Click “OK” to apply the grouping.
Excel will now group the dates in your défenseur autel based on the chosen grouping possibilité. The défenseur autel will display the summarized data based on the grouped dates.
Compté: The steps may slightly vary depending on your Excel reprise. If you don’t see the “Group” possibilité in the context table, you can also access the Grouping dialog box by going to the “PivotTable Analyze” or “PivotTable Tools” tab on the Excel ribbon, selecting the “Group Field” button, and following the subsequent steps.
By grouping dates in your défenseur autel, you can easily analyze data by specific time periods, such as months, which can help you get a clearer understanding of trends and patterns in your data.
How to Add a Calculated Field in a Arbre Tertre
If you’re trying to add a calculated field in a défenseur autel in Excel, you can follow these steps:
Select any cell within the défenseur autel.
Go to the “PivotTable Analyze” or “PivotTable Tools” tab on the Excel ribbon (depending on your Excel reprise).
Go to the “Calculations” group. In the “Calculations” group, click on the “Fields, Items & Sets” button and select “Calculated Field” from the dropdown table.
The “Insertion Calculated Field” dialog box will appear. Écussonner a name for your calculated field in the “Name” field.
Écussonner the formula for your calculated field in the “Formula” field. You can use mathematical operators (+, -, *, /), functions, and references to other fields in the défenseur autel.
Click “OK” to add the calculated field to the défenseur autel.
The défenseur autel will now display the calculated field as a new column or row, depending on the typologie of your défenseur autel.
The calculated field you created will use the formula you specified to calculate values based on the existing data in the défenseur autel. Pretty calme right?
Compté: The steps may slightly vary depending on your Excel reprise. If you don’t see the “Fields, Items & Sets” button, you can right-click on the défenseur autel and select “Spectacle Field List.” They both do the same thing.
Adding a calculated field to your défenseur autel helps you perform buté calculations and get new insights from the data in your défenseur autel.
It allows you to expand your analysis and perform calculations specific to your needs. You can also watch the video below for some visual instructions.
How to Remove Abondant Fini From a Arbre Tertre
To remove the large in extenso from a défenseur autel in Excel, follow these steps:
Select any cell within the défenseur autel.
Go to the “PivotTable Analyze” or “PivotTable Tools” tab on the Excel ribbon (depending on your Excel reprise).
Click on the “Field Settings” or “Options” button in the “PivotTable Options” group.
The “PivotTable Field Settings” or “PivotTable Options” dialog box will appear.
Depending on your Excel reprise, follow one of the following methods:
For Excel 2013 and earlier versions: In the “Subtotals & Filters” tab, uncheck the box next to “Abondant Fini.”
For Excel 2016 and later versions: In the “Totals & Filters” tab, uncheck the box next to “Spectacle large totals for rows/columns.”
Click “OK” to apply the changes.
The large in extenso row or column will be removed from your défenseur autel, and only the subtotals for individual rows or columns will be displayed.
Compté: The steps may slightly vary depending on your Excel reprise and the typologie of your défenseur autel. If you don’t see the “Field Settings” or “Options” button in the ribbon, you can right-click on the défenseur autel, select “PivotTable Options,” and follow the subsequent steps.
By removing the large in extenso, you can foyer on the specific subtotals within your défenseur autel and exclude the overall summary of all the data. This can be useful when you want to analyze and present the data in a more detailed manner.
For a more visual explanation, watch the video below.
7 Tips & Tricks For Excel Arbre Tables
1. Use the right data range.
Before creating a défenseur autel, make sure that your data range is properly selected. Include all the necessary columns and rows, making sure there are no empty cells within the data range.
2. Taille your data.
To avoid potential issues with data interpretation, largeur your data properly. Ensure consistent formatting for instant fields, numeric values, and text fields.
Remove any leading or trailing spaces, and ensure that all values are in the congru data typique.
3. Choose your field names wisely.
While creating a défenseur autel, use clear and descriptive names for your fields. This will make it easier to understand and analyze the data within the défenseur autel.
4. Apply défenseur autel filters.
Take advantage of the filtering capabilities in défenseur tables to foyer on specific subsets of data. You can apply filters to individual fields or use slicers to visually interact with your défenseur autel.
5. Classify your data.
If you have a déployé amount of data, consider grouping it to make the analysis simpler. You can group data by dates, numeric ranges, or with your special kind of typage.
This helps to summarize and organize data in a more meaningful way within the défenseur autel.
6. Customize défenseur autel typologie.
Excel allows you to customize the typologie of your défenseur autel.
You can drag and drop fields between different areas of the défenseur autel (e.g., rows, columns, values) to rearrange the typologie and present the data in the most useful way for your analysis.
7. Refresh and update data.
If your data introduction changes or you add new data, remember to refresh the défenseur autel to reflect the latest updates.
To refresh a défenseur autel in Excel and update it with the latest data, follow these steps:
Select the défenseur autel. Click anywhere within the défenseur autel that you want to refresh.
Refresh the défenseur autel. There are nombre ways to refresh the défenseur autel:
Right-click anywhere within the défenseur autel and select “Refresh” from the context table.
Or, go to the “PivotTable Analyze” or “PivotTable Tools” tab on the Excel ribbon (depending on your Excel reprise) and click on the “Refresh” button.
Or, use the keyboard shortcut: Alt+F5.
Verify the updated data. After refreshing, the défenseur autel will update with the latest data from the introduction range or data connection. We recommend confirming the refreshed data to make sure you have what you want.
By following these steps, you can easily refresh your défenseur autel to reflect any changes in the underlying data. This ensures that your défenseur autel always displays the most up-to-date actualité.
You can watch the video below for more detailed instructions.
These tips and tricks will help you create and use défenseur tables in Excel, allowing you to analyze and summarize your data in a dynamic and compétent manner.
Digging Deeper With Arbre Tables
Imagine this. You’re a commerce analyst. You have a déployé dataset that needs to be analyzed to identify trends and patterns. You and your team decide to use a défenseur autel to summarize and analyze the data quickly and efficiently.
As you explored different combinations of fields, you discovered interesting insights and correlations that would have been time-consuming to find manually.
The défenseur autel helped you to streamline the data analysis process and present the findings to stakeholders in a clear and concise manner, impressing them with your team’s efficiency and ability to retrieve actionable insights. Sounds good right?
You’ve now learned the basics of défenseur autel creation in Excel. With this understanding, you can allégorique out what you need from your défenseur autel and find the solutions you’re looking for. Good luck!
Editor’s glose: This post was originally published in December 2018 and has been updated for comprehensiveness.
