To see how to create a pivot table from data on different sheets, watch this short video. If you can't combine your data on a single worksheet, another solution is to create named ranges in an Excel file, and use Microsoft Query (MS Query) to combine the data.Create a Union Query Manually Union Query Macro -- Sheets in One File Union Query Macro -- Data in Multiple Excel Files In Excel, you can open the Microsoft Query tool, and write a SQL statement to create a Union query (full outer join) to combine multiple tables.If you need to combine data in multiple files, here are a couple of options, using macros provided by Excel expert, Kirill Lapin.Pivot Table - The first example works on multiple files, which must have the data in identical structures, and you can read the instructions on my blog.The updates will be delivered to you via a FREE Web Edition of this book, which can be accessed with any Internet connection. About Mr Excel Library: Every book in the Mr Excel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website Mr Excel.com, these books will• Dramatically increase your productivity–saving you 50 hours a year or more• Present proven, creative strategies for solving real-world problems• Show you how to get great results, no matter how much data you have• Help you avoid critical mistakes that even experienced users make Introduction 1PART I : MASTERING EXCEL RANGES AND FORMULAS Chapter 1 Getting the Most Out of Ranges 5Advanced Range-Selection Techniques 5 Mouse Range-Selection Tricks 6 Keyboard Range-Selection Tricks 7 Working with 3D Ranges 7 Selecting a Range Using Go To 8 Using the Go To Special Dialog Box 9Data Entry in a Range 14Filling a Range 14 Using the Fill Handle 15 Flash-Filling a Range 18Creating a Series 20Advanced Range Copying and Pasting 21 Pasting Selected Cell Attributes 22 Combining Two Ranges Arithmetically 23 Transposing Rows and Columns 24Clearing a Range 25Applying Conditional Formatting to a Range 25 Creating Highlight Cells Rules 26 Creating Top/Bottom Rules 27 Adding Data Bars 29 Adding Color Scales 32 Adding Icon Sets 33Chapter 2 Using Range Names 37Defining a Range Name 38 Working with the Name Box 39 Using the New Name Dialog Box 40 Changing the Scope to Define Sheet-Level Names 41 Using Worksheet Text to Define Names 41 Naming Constants 44Working with Range Names 45 Referring to a Range Name 45 Working with Auto Complete for Range Names 47 Navigating Using Range Names 47 Pasting a List of Range Names in a Worksheet 48 Displaying the Name Manager 48 Filtering Names 48Editing a Range Name’s Coordinates 49Adjusting Range Name Coordinates Automatically 49Changing a Range Name 51Deleting a Range Name 51Using Names with the Intersection Operator 51Chapter 3 Building Basic Formulas 53Understanding Formula Basics 53 Formula Limits in Excel 2016 54 Entering and Editing Formulas 54 Using Arithmetic Formulas 55 Using Comparison Formulas 56 Using Text Formulas 57 Using Reference Formulas 57Understanding Operator Precedence 57 The Order of Precedence 58 Controlling the Order of Precedence 58Controlling Worksheet Calculation 60Copying and Moving Formulas 62 Understanding Relative Reference Format 62 Understanding Absolute Reference Format 64 Copying a Formula Without Adjusting Relative References 65Displaying Worksheet Formulas 65 Displaying All Worksheet Formulas 65 Displaying a Cell’s Formula by Using FORMULATEXT() 65Converting a Formula to a Value 66Working with Range Names in Formulas 67 Pasting a Name into a Formula 67 Applying Names to Formulas 68 Naming Formulas 70Working with Links in Formulas 71 Understanding External References 72 Updating Links 73 Changing the Link Source 73Formatting Numbers, Dates, and Times 74 Numeric Display Formats 74 Date and Time Display Formats 83 Deleting Custom Formats 85Chapter 4 Creating Advanced Formulas 87Working with Arrays 87 Using Array Formulas 88 Using Array Constants 91 Functions That Use or Return Arrays 91Using Iteration and Circular References 93Consolidating Multisheet Data 95 Consolidating by Position 95 Consolidating by Category 98Applying Data-Validation Rules to Cells 100Using Dialog Box Controls on a Worksheet 103 Displaying the Developer Tab 103 Using the Form Controls 103 Adding a Control to a Worksheet 104 Linking a Control to a Cell Value 104 Understanding the Worksheet Controls 105Chapter 5 Troubleshooting Formulas 111Understanding Excel’s Error Values 112 #DIV/0!
gives three pieces of information: the workbook, the worksheet, and the cell you linked to in the worksheet.
The first element of the reference, the name of the workbook, is enclosed in brackets; the end of the second element (the worksheet) is marked with an exclamation point; and the third element, the cell reference, has a dollar sign before both the row and the column identifier.
The single quotes around the workbook name and worksheet name are there to allow for the space in the Truck Fuel worksheet’s name.
However, it's a bit tedious to set up, especially if you have more than a couple of tables.
You can read more about MS Queries here: Instead of manually setting up a union query, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz.