Trending March 2024 # How To Find Circular References In Excel To Avoid Faulty Data # Suggested April 2024 # Top 4 Popular

You are reading the article How To Find Circular References In Excel To Avoid Faulty Data updated in March 2024 on the website Hatcungthantuong.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 How To Find Circular References In Excel To Avoid Faulty Data

Did you see the circular reference error on an Excel sheet and wondering if the worksheet is broken? Trust me! You’re not the first one to face this Excel error. Hundreds and thousands of Excel users experience circular reference errors every day. Not to worry! Here’s how to find circular references in Excel and resolve those.

When someone changes the cell values of the cell references, the worksheet updates itself automatically. This is called data analysis automation and data analysts love to play with cell references.

However, the problem arrives when you intentionally or accidentally type the cell address where you’re currently performing a calculation. Or, when you’re creating many formulas throughout the worksheet, a cell containing a formula appears in another formula indirectly.

In such and many other similar situations you get the circular reference error in Excel. Read on to learn what circular reference is, its reasons, why to avoid it, and most importantly how to find circular references in Excel to get rid of these anomalies for good.

What Is a Circular Reference in Excel?

Excel encounters a circular reference error when it sees that one or many functions of the worksheet reference back to the cell address where their functions were created.

It might sound confusing to you, so think about how severe it could be for the Excel app. In short, you never want to put the cell address in a formula where you’re creating it. Look at the following example for a visual clarification:

You’re trying to multiply 10 from cell B1 and put the result in cell B1 itself.

Now, in the above scenario, if you just press OK, Excel will close the error and show zero as the calculated result. This is just a straightforward example.

Now, imagine there are hundreds of formulas in a bookkeeping worksheet, finance planning worksheet, employee payroll, vendor payment worksheet, and so on.

You’ll trust the faulty data calculated by Excel due to the circular reference error and that’ll in turn impact your business or professional decision-making.

Furthermore, circular referencing can severely reduce the speed of calculations within the Excel app. In severe cases, the very error can cause extremely slow performance in the PC too if not dealt with immediately.

For example, in energy production units, like windmills, you need to calculate recycled output energy from a fraction of input energy. Alternatively, in a chemical plant, engineers use the output stem as input for the same or another boiler or process vessel. Such high-level engineering calculations indeed require circular references for iterative calculations.

When Do You See the Circular Reference Error in Excel?

Clearly speaking, the circular reference pop-up isn’t an error message. It’s a way for Excel to inform you that it encountered circular reference in one or many formulas and that may influence its calculation algorithm and performance.

To avoid any damages caused by circular references in Excel formulas, you must know when they appear and when they don’t. Find below the instances:

In any open worksheet or workbook, you create a formula and enter the circular reference for the first time.

You or someone removed all the circular references from the Excel file and saved it. Now, again you or another user enter a circular reference in the same file.

You closed all open workbooks. Now, opened a different workbook and then type a formula containing a circular referencing.

You’ve just opened a workbook that previously contained one or many circular references. You’ll see the warning.

Thus, you must carefully look for the above instances to catch circular reference warnings and resolve those if you’re not looking for iterative calculations.

Types of Circular References in Excel

If you want to learn how to find circular references in Excel, you must understand their types. Because finding and eliminating circular references in Excel isn’t an automated task. Find below the common types of this error:

Intended circular references occur when you use them intentionally for iterative calculation purposes. For example, you need to input a time stamp on another cell when someone enters a value in a referenced cell using volatile and logical functions like IF, OR, SUMIF, etc.

Unintended circular referencing when you try to work on an Excel formula faster and refer to the formula-containing cell mistakenly. For instance, you need to apply the SUM function from cell A1 to A9 and fetch the result in cell A10. But mistakenly, you type the formula like this on cell A10:

=SUM(A2:A10)

Hidden circular references are those that don’t show any warning messages when you create a formula or open the worksheet for the first time. It happens because someone allowed iterative calculation on the Excel file.

Indirect circular references are also popular and they occur when you create a new formula in any cell, for instance, cell A1, and this new formula would use another function in another cell, like cell B1, which already is using cell A1 as a reference.

Now that you understood the basics of circular references in Excel, let’s find out how to find circular references in Excel below:

How to Find Circular References in Excel: From Status Bar

Provided that the iterative calculation feature is deactivated, you can see the following notifications about circular references on the Excel Status Bar:

Circular References: C3 (or any other cell reference) when you’re on a worksheet containing circular formula in the workbook.

You’ll only see Circular References if you’re on a worksheet that doesn’t contain any but the workbook has a few of these circular formulas.

How to Find Circular References in Excel: Using Error Checking

It’s the best way to check for circular references in a workbook or worksheet. Here are the steps you must know:

Open a worksheet that you suspect of containing circular formulas.

Hover the cursor over the Circular References option to see all the available instances in all worksheets.

Select each instance and Excel will pinpoint those cells automatically.

How to Find Circular References in Excel: From Formula Tracing

If you don’t find the Error Checking method efficient enough to detect large numbers of formulas with circular references, you can try these steps to use the formula tracing method:

Select any referenced cell or the formula-containing cell.

Excel will highlight the cells that affect the formula result on the current cell with arrows.

Repeat the steps on all worksheets to check the entire workbook.

This method is particularly important when the iterative calculation features are active on the Excel worksheet.

How to Find Circular References in Excel: Using Excel Options

On the Excel Options dialog box, select the Formulas menu from the sidebar and then uncheck the checkbox for Enable iterative calculation.

Now, the workbook or worksheet will automatically show you the circular reference warning.

If it doesn’t show instantly, save the workbook, close it, and reopen it to see the warning.

Once you see the warning check the Excel Status Bar to know which cell is in the circular reference.

How to Resolve Circular References in Excel

The sureshot way to correct one or many circular reference errors on Excel is by manually editing each formula that contains it. In this way, you can resolve both accidental and indirect circular references in Excel.

Another easy way is to remove the formula from the current cell and place it somewhere else within the same worksheet.

How to Find Circular References in Google Sheets (Bonus Method)

Like Excel, Google Sheets doesn’t allow any circular references in one or more formulas if you haven’t activated the iterative calculation feature. Hence, Google Sheets will immediately show the #REF! error on the cell that contains a circular formula.

Therefore, you can simply select all the cells that contain the #REF! warning to find and resolve circular references on Google Sheets.

Excel Circular References: Final Words

So, now you know all the secrets for how to find circular references in Excel. Use the method you like and ditch all the circular references to ensure you don’t get confused by unreliable data from an Excel report or mathematical dashboard.

You're reading How To Find Circular References In Excel To Avoid Faulty Data

Find And Remove Duplicates In Excel

Watch Video – How to Find and Remove Duplicates in Excel

With a lot of data…comes a lot of duplicate data. 

Duplicates in Excel can cause a lot of troubles. Whether you import data from a database, get it from a colleague, or collate it yourself, duplicates data can always creep in. And if the data you are working with is huge, then it becomes really difficult to find and remove these duplicates in Excel.

In this tutorial, I’ll show you how to find and remove duplicates in Excel.

Duplicates in Excel can come in many forms. You can have it in a single column or multiple columns. There may also be a duplication of an entire row.

Conditional Formatting makes it simple to highlight duplicates in Excel.

Here is how to do it:

Select the data in which you want to highlight the duplicates.

In the Duplicate Values dialog box, select Duplicate in the drop down on the left, and specify the format in which you want to highlight the duplicate values. You can choose from the ready-made format options (in the drop down on the right), or specify your own format.

This will highlight all the values that have duplicates.

Quick Tip: Remember to check for leading or trailing spaces. For example, “John” and “John ” are considered different as the latter has an extra space character in it. A good idea would be to use the TRIM function to clean your data.

If you have data that spans multiple columns and you need to look for duplicates in it, the process is exactly the same as above.

Here is how to do it:

Select the data.

In the Duplicate Values dialog box, select Duplicate in the drop down on the left, and specify the format in which you want to highlight the duplicate values.

This will highlight all the cells that have duplicates value in the selected data set.

Finding duplicate data and finding duplicate rows of data are 2 different things. Have a look:

Finding duplicate rows is a bit more complex than finding duplicate cells.

Here are the steps:

Drag this down for all the rows. This formula combines all the cell values as a single string. (You can also use the CONCATENATE function to combine text strings)

By doing this, we have created a single string for each row. If there are duplicate rows in this dataset, then these strings would be exactly the same for it.

Now that we have the combined strings for each row, we can use conditional formatting to highlight duplicate strings. A highlighted string implies that the row has a duplicate.

Here are the steps to highlight duplicate strings:

Select the range that has the combined strings (E2:E16 in this example).

In the Duplicate Values dialog box, make sure Duplicate is selected and then specify the color in which you want to highlight the duplicate values.

This would highlight the duplicate values in column E.

In the above approach, we have highlighted only the strings that we created.

But what if you want to highlight all the duplicate rows (instead of highlighting cells in one single column)?

Here are the steps to highlight duplicate rows:

Drag this down for all the rows. This formula combines all the cell values as a single string.

Select the data A2:D16.

This formula would highlight all the rows that have a duplicate.

In the above section, we learned how to find and highlight duplicates in excel. In this section, I will show you how to get rid of these duplicates.

If you have the data in a single column and you want to remove all the duplicates, here are the steps:

Select the data.

In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Make sure the column is selected (in this case there is only one column).

This would remove all the duplicate values from the column, and you would have only the unique values.

CAUTION: This alters your data set by removing duplicates. Make sure you have a back-up of the original data set. If you want to extract the unique values at some other location, copy this dataset to that location and then use the above-mentioned steps. Alternatively, you can also use Advanced Filter to extract unique values to some other location.

Suppose you have the data as shown below:

To delete the duplicate row in this case:

Select the data.

In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Select all the columns

except the Date column.

This would remove the 2 duplicate entries.

NOTE: This keeps the first occurrence and removes all the remaining duplicate occurrences.

To delete duplicate rows, here are the steps:

Select the entire data.

In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Select all the columns.

Use the above-mentioned techniques to clean your data and get rid of duplicates.

How To Sum Multiple Rows In Excel

How to Sum Multiple Rows in Excel (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Sum Multiple Rows in Excel

MS Excel assists with everyday office work like analyzing data, re-calculation, V lookup, H lookup, etc. Some of us have our own personal Excel consisting of daily requirements to keep a check on our expenses. At least I have one!

Here, it is very important to understand the usage of the SUM function while there are multiple rows and columns. In this context, we will specifically learn about it in the case of multiple rows. Ample data may be provided, which may take a lot of time. Let us learn about the sum, which may help us save time with the calculations.

What is SUM Function? Examples to Sum Multiple Rows in Excel

Let us now begin exploring different ways to sum multiple rows in Excel with the help of the following examples.

You can download this How to Sum Multiple Rows Excel Template here – How to Sum Multiple Rows Excel Template

Example #1 – SUM Function Used For Number of Cells in a Single Column

The basic way to perform the SUM function is in the following form.

Step 1: When we press “Alt +” or “= “, the screenshot looks as follows.

Step 2: Press Enter Key, and we get the following result.

The above is one way of performing the sum of multiple rows. We have yet another way of doing it. In the below-mentioned example, the sum is performed with the help of an in-built function in MS Excel.

Example #2 – SUM Function Used For Adding Up Selective Cells

In another example that we are taking up, we can sum optional cells. While we have the Summing up option for the rows with the help of examples explained above, we have another way of choosing only a few cells for summing up.

Press Enter Key, and the result is as follows.

Example #3 – Summing Up in Another Cell

Suppose in the same example, we require the total in cell B2. So, the procedure is as follows.

Step 1: Apply SUM Formula in cell B2.

Example #4 – Same Numbers are Placed Horizontally

Step 1: Apply the SUM Formula in cell G2.

Step 2: After pressing Enter Key, the result is as follows.

Example #5 – Numbers are Placed Vertically

For the above explanation, we have taken the following chart full of numbers.

Step 1: In column G, put the function =Sum( and then press the left arrow and bring it upwards towards cell F2.

Example #6 – Sum Function Used for Selective Products from a List

Another use of a sum function is using 2 functions together, Sum and IF. Let us learn about that with the help of the following example.

With the above examples, we now understand the function Sum and how multiple rows can be used to Sum the numbers. The data for the same is as follows:

Step 1: Enter SUMIF Formula in cell C2.

Step 2: Press Enter key, and the following result is displayed.

Things to Remember

The function sum can be used for adding as many numbers as provided. If multiple rows and columns must be summed up, we can navigate through Shift + Ctrl + Arrow keys to our selection area.

For selective numbers to be summed up, we should use Sum(Number 1, Number 2,.……………) as shown in Example 2. However, if we have been to choose from products and sum up the numbers from corresponding rows, then the SUMIF function is recommended, as explained in Example 6.

Recommended Articles

This is a guide on How to Sum Multiple Rows in Excel. Here we discuss How to Sum Multiple Rows in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

How To Make A Histogram In Excel

A histogram is a type of chart you can generate from data in Excel. It makes it easy to summarize the frequency of particular values in your dataset. Excel makes it simple to create a histogram, assuming that a histogram is actually what you need!

What Is a Histogram?

A histogram is a type of chart that uses vertical bars to summarize ranges of data. While it may look like a bar chart, there are significant differences. Bar charts show the differences among variables, whereas histograms are generally used to show the differences among variables in terms of another variable.

Table of Contents

To illustrate, a histogram may be used to show us how common ranges of IQ scores are. Each bar represents a “bin” or range of scores. So something like 0-10,11-20, etc. 

The vertical Y-axis shows us how many measurements of that variable fall within each bin range. So if you have 100 people write an IQ test, every person whose score falls within a particular bin is counted towards the frequency score of that bin.

With a bar chart, you might want to compare something like average IQ scores between countries. In this case, each bar might represent a country and the vertical Y-axis would represent the average IQ of that country.

When Should You Use a Histogram?

HIstograms are a visualization of frequency distribution. It can help you see, at a glance, what sort of distribution your data has. For example, the “Normal Distribution” has the distinctive bell-curve look. A bimodal distribution will have two bumps. You can also see if score frequencies are skewed one way or another. 

Of course, if you really want to determine whether your frequency distribution is normal or not, you’d run a normality test in Excel on your data. Those tests still use histograms as a basis though and creating and observing a histogram is a crucial first step in showing you roughly what sort of distribution you may be dealing with.

What You Need To Make a Histogram

In order to make a histogram, you need a few things:

A set of measurements for a single variable.

Defined “bins” of value ranges.

The first requirement is fairly straightforward. For example, if you have the weights of a group of people, you’d have each measured weight recorded in your dataset. Be careful not to mix the data from groups you don’t want to measure together into one histogram. For example, if you only wanted to look at the weight distribution of a certain age group or gender, you should only include data for that group.

If you wanted to compare the frequency distributions between two groups on a single variable, you’d need multiple histograms. One for each population group.

All About Bins

The next requirement is the trickiest. You need to decide on the “bins” that your frequency counts will be sorted into. The problem is that these may be arbitrary. If you’re going to look at the frequency of scores between 0 and 100, you could have 100 bins, one for each possible score. However, that means 100 bars in your histogram. 

That’s a finely-grained distribution, but it’s probably not all that useful. In the case of test scores, you’re in luck since there are already “bins” in the form of grade symbols. So you could arrange your bins to coincide with those. However, for other types of data you have to invent the bin ranges.

Spend some time considering how you’d like to divide scores into bins and whether the histogram will paint the picture you’re looking for if you decide on a particular “bin width”. 

You can also choose to leave it to an automatic function in Excel, where it will try to decide on a bin width that’s best suited to your data. In Excel, you can also specify the number of bins, which includes optional so-called overflow- and underflow- bins. These capture all scores over and under a specified value.

Creating a Histogram in Excel: Step-by-Step Create the Histogram

Assuming you’ve entered all the values for your dataset, select all the values that should be included in the histogram.

Next, switch to the Insert tab.

Now, under the chart section, select on the picture that looks like a histogram/bar chart. 

From the popup menu, select histogram.

Customize the Horizontal Axis

Now your histogram is in the sheet, but it probably doesn’t look the way you want it to. So next, we’re going to customize the horizontal axis:

The format axis pane will now be open. There are a number of important options here that you can use to tune your histogram so that it looks exactly like you need it to.

Under Axis Options, you can customize the bins we discussed earlier. The two most important settings here are bin width and the number of bins. These options are mutually exclusive. If you specify a bin width in numbers, the number of bins will change automatically and vice versa. You can choose to activate overflow and underflow bins here as well.

From Hysteriagram to Histogram

Hopefully you can now make a histogram easily, but if you need to review basic Excel concepts, try reading Microsoft Excel Basics Tutorial – Learning How to Use Excel

Excel Find Column Containing A Value

Last week I had an email from Mike asking how he can lookup a suburb in a range of columns and return the post code from the header row.

I imagine his data was a bit like this:

And in cell B9 he wants to find the post code for Herston.

One way is with this array formula:

=

INDEX(B1:F1,,

MIN(

IF(B2:F5=A9,COLUMN(A:E))

)

)

Entered with CTRL+SHIFT+ENTER.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook . Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.

Before we dive in, here are the syntaxes for the INDEX and IF functions as a reminder (I’ve crossed out the arguments we’re not using):

INDEX(reference,

row_num

,[column_num],[

area_num

])

IF(logical_test, [value_if_true],[

value_if_false

])

The INDEX formula is returning a reference to the cell in the first row for the column containing ‘Herston’. For the column_num argument it uses a combination of IF, COLUMN and MIN.

Here it is again for reference:

=

INDEX(B1:F1,,

MIN(

IF(B2:F5=A9,COLUMN(A:E))

)

)

Check the cells in the range B2:F5 for ‘Herston’ and tell INDEX what column number it’s in. i.e. column 4. INDEX (look in) the range B1:F1 and return a reference to the 4th cell i.e. E1, which contains 4006.

So what’s MIN got to do with it….hold your horses, more on that in a moment.

Let’s step through the formula in the order it evaluates:

Step 1 – IF function’s logical_test: B2:F5=A9 i.e. B2:F5=Herston and it looks like this:

=INDEX(B1:F1,,MIN(IF({FALSE,FALSE,FALSE,FALSE,FALSE;  FALSE,FALSE,FALSE,FALSE,FALSE; FALSE,FALSE,FALSE,

TRUE

,FALSE;  FALSE,FALSE,FALSE,FALSE,FALSE},,COLUMN(A:E))))

Tip: did you notice in the formula above there is a semicolon after every 5th ‘FALSE’ instead of a comma. This semicolon represents a new row in the array.

Or if you imagine our formula is putting together a list of values representing each row in the table like this:

Step 2 – COLUMN function: This evaluates to return a horizontal array of numbers {1,2,3,4,5} for our IF function’s value_if_true argument. These numbers represent the 5 columns B:F in our table.

Our formula now looks like this:

=INDEX(B1:F1,,MIN(IF({FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,

TRUE

,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE},{1,2,3,

4

,5})))

Tip: Instead of using the COLUMN function to generate the array of numbers we could simply type {1,2,3,4,5} into the formula. However, with large horizontal arrays it’s quicker (and dynamic) if we use the COLUMN function to generate the array, or for vertical arrays you can use the ROW function.

Note: if you don’t want your COLUMN function to be dynamic you can use the INDIRECT function to fix it, like this:

=INDEX(B1:F1,,MIN(IF(B2:F5=A9,COLUMN(INDIRECT("A:E")))))

Step 3 – IF function value_if_true: The IF function finishes evaluating by assigning the value_if_true numbers (generated by the COLUMN function) to the TRUE results in the logical_test.

To visualise this we can look at the 3rd horizontal array (i.e. the series of FALSE/TRUE after the second semicolon below). Remember this is the 3rd row of our table above.

Excel gives the TRUE results the corresponding number from the array generated from the COLUMN function {1,2,3,4,5} like so:

Note: In this step the FALSE values evaluate to nothing i.e. they are ignored. Remember we don’t have a value_if_false argument in our IF formula. Our formula now looks like this:

Step 4 – MIN: This simply evaluates to find the one and only number; 4.

=INDEX(B1:F1,,

4

)

Tip: Since there is only one number remaining (the rest are all FALSE) we could have used MAX or SUM to get the same result as MIN.

Step 5 – INDEX: Finally INDEX can return a reference to the 4th column in the range B1:F1 which is cell E1 containing post code 4006.

Tip: Notice how our INDEX formula doesn’t have a row_num argument:

Since our reference is only one row high we don’t have to type a 1 in for the row_num argument, we simply enter a comma as a placeholder and continue on to the column_num argument.

What The?

Did you find that tricky?

When working with long or complex formulas I like to use the Evaluate Formula tool to understand what’s going on behind the scenes.

You can also evaluate parts of your formula by highlighting the section of the function in the formula bar and pressing the F9 key. Below I’ve evaluated the COLUMN(A:E) part of my formula:

To revert to the original formula either press the escape key or CTRL+Z.

Thanks

Thanks to Mike for inspiring this post.

If you liked this please share it with your friends and colleagues.

How To Create Relational Databases In Excel 2013

Not anymore: Excel 2013’s table tools include features that make it easy to link charts and cells, perform searches, and create dynamically updated reports, just like—yes—a relational database. Excel can handle a lot of day-to-day office data this way, and we’ll show you how to set it up.

How Excel makes a relational database

To show you how Excel makes it easier, we will create two tables: the master table and the detail table. The master table is the primary table, which generally contains unique records (such as name, address, city, state, etc.). This table rarely changes except to, say, add or delete individuals.

For every record in the master table, there can be many records in the detail tables (also called slave or child tables) that link back to the master table. This is called a one-to-many relationship. The data in the detail tables—such as daily sales, product prices, quantities—usually changes constantly.

To avoid repeating all the master information in every detail table, you create relationships using one unique field, such as the Sales ID, then let Excel do the rest. For example, you have 10 sales people who all have unique, demographic information (master table). Each sales person has 200 products that he/she sells (detail or child table). At the end of each year, you need a report that provides the total yearly sales by person, but you also need a report that provides the total sales by city.

For this tutorial, we’ll create a master table with the salespersons’ information and a second table that provides their total sales, by quarter, for the year. The Sales ID is the relational field that connects the tables. Then, we’ll create a report (or pivot table) that shows which cities had the highest sales.

Open Excel and select a new, blank worksheet.

Create the master table

2. In cell A1 type: Master. In cells A3 through F3 type these column headers: Sales ID, Sales Person, Address, City, State, Zip Code.

3. In cells A4 through A13 type the sales ID numbers—in this case, 101 through 110. The Sales ID is the unique data value that’s used to create a relationship between your two tables.

4. Enter names, addresses, cities, states, and zip codes in the remaining cells. You can copy the information from this sample worksheet or create your own data. Since we are looking for the highest sales by city, be sure to create multiple cities in your table. For example, we have three salespeople in Los Angeles, two in Hollywood, two in San Francisco, and three in San Diego.

Create the master table.

6. With the table still highlighted, select the Design tab under the text that says Table Tools (this option is available only when the table is highlighted). In the Properties group (far left), in the box under Table Name, type Master.

Highlight and name the table.

Create the detail table

2. In cell A1, type Total Sales for 2013. In cells A3 through E3, type Sales ID, Quarter1, Quarter2, Quarter3, and Quarter4.

3. In cells A4 through A13 type the sales ID numbers: 101 through 110.

4. In B4 through E13, enter 40 random numbers that represent sales dollars or copy the data from this example table.

Create the detail (Sales) table.

6. With the table still highlighted, select the Design tab under the text that says Table Tools (this option is only available when the table is highlighted). In the Properties group, in the box under Table Name, type Sales.

Highlight and name the detail (Sales) table.

Set relationships in the pivot table report section

The first rule of pivot tables: You must define the table relationships within the Pivot Table report section. Do not attempt to create the relational connections first, because Excel will not recognize them from the Pivot Table reporting section. Also, be sure to select the detail table (Sales) for the “analyze data” table, otherwise it won’t work.

Insert and create the Pivot Table.

The Pivot Table menus appear with a Help box on the left that says “To build a report, choose fields from the Pivot Table field list.”

Select fields from sales and master tables, then create relationship.

Excel makes the connection, then displays the report on the screen: Total Sales by City. Enter a report title in A1, and it’s complete.

Total sales by city report.

Sort, create filters, and select data by other fields

Label Filters and Value Filters are additional filtering options to help you refine your search. For example, in the Label Filters, if you choose all cities that Begin With “S,” you get San Diego and San Francisco. If you choose all cities Less Than “S,” you get Hollywood and Los Angeles. Numeric fields are filtered the same way most all other databases do it—Less Than, Greater Than, Equals, Between, etc.

Sort and filter by City for custom results.

3. You can also select a different field and quickly create a new report. For example, if you’d like to see the quarterly sales plus totals by sales person, uncheck City and check Sales Person. The report drops in.

The Pivot Tables/database options are endless. There are numerous ways to analyze the data, create and manage sets, group fields, insert slicers and timelines, drill up and down, and import and export data, as well as design reports with custom layouts and styles, create hundreds of colorful charts, then print it all out for distribution.

Total sales by sales person, then filter by selected sales persons.

Charts and styles

To spice up your table before you print it, try adding a chart and/or some colors and style to the table.

Add flair with charts and styles.

With this new relational database/table feature, this process is so easy that once it’s set up in Excel, you can extract specific data and create dozens of reports in minutes.

Update the detailed information about How To Find Circular References In Excel To Avoid Faulty Data on the Hatcungthantuong.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!