Second, if you click into the reference, Excel will show you the range on the worksheet.Įxcel can also name multiple ranges at the same time using the Create from Selection button. Note two things here: first, Excel can often guess the name for the range as long as the data is clearly labeled. Excel will then open the New Name dialog box, where I can give the range a name, set the scope, provide a description and, finally, select the cells that the name should refer to. You can also name ranges more formally using the controls that appear in the Formulas tab of the ribbon.įor example, I can name the Beds column by first selecting the data and then clicking Define name. Now I can refer to the prices in other formulas by using that name. I'll follow the same process with the Price column. Now I can easily refer to that name in a formula. Simply select the cells you want to name, and type the name in the box.įor example, to give the data in this table a name, first select the cells that include the data, then enter a name in the name box. The simplest way to create a named range is to use the name box, which sits to the left of the formula bar. Let's take a look at a few ways to create named ranges. They make your formulas much easier to read and understand they automatically give you absolute references, and they reduce errors. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.Named ranges are one of the most useful features in Excel. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. It will lead us to the quick correction of the error. Next time we encounter the “Source reference not Valid Error”, we only have to follow the checklist discussed in this article.
Output: Pivot table created with the range “Data” Creating a pivot table with a named rangeįigure 12. We can now successfully create a pivot table using this range.įigure 11. Click the Refers to bar and select cells B2:E6 to define the range “Data”. In the Name Manager dialog box, click New. Creating a named range using Name Manager Click the Formulas tab, then select Name Manager.
We first define a named range before we use it to create a pivot table. The error naturally exists because we tried to create a pivot table basing the values on the range “ Data ”, when in fact, the range does not exist and is not defined. Error in creating a pivot table with a named range The “ Data source reference is not valid. In the Create PivotTable dialog box, enter “ Data ” into the Table/Range bar. Select cell G2, then click the Insert tab. Let’s use below data and create a pivot table. When inserting a pivot table with a named range, make sure the range exists and is defined.Save the Excel file to the computer or local drive.Renaming the Excel file to remove square brackets Remove the brackets by renaming the file to “ Correcting the Source Reference not Valid Error in a Pivot Table.xlsx”.įigure 3. Note the square brackets in the word Correcting. Error in creating a pivot tableĪs shown, the file name is “ the Source Reference not Valid Error in a Pivot Table.xlsx”. When we try to insert a PivotTable, an error is displayed and the pivot table is not created.įigure 2. If it contains any square brackets, rename the file and remove the brackets.īelow is an example of a simple table with range B2:E6. The data source refers to a named range with invalid referenceĬhecklist to Correct the Source Reference Not Valid Error.The pivot table data source refers to a non-existent range.File comes from the email or on the web and is not saved in the local drive.Excel file name contains the characters “ ” or square brackets.Most probably, the reason for the error is one or more of the following: “Data source reference is not valid” error message in Excel When working with pivot tables, we sometimes encounter the error “Data source reference is not valid”.įigure 1. Correcting the Source Reference not Valid Error in a Pivot Table
0 kommentar(er)
