Combine Excel Sheets with *this* simple formula

Summary

This video script introduces a streamlined method for consolidating budget data in Excel using the 'VSTACK' formula, which simplifies the process of combining data from multiple sheets. The tutorial demonstrates how to create a dynamic formula that automatically updates when new sheets are added, and how to use the 'FILTER' function to remove zeros and consolidate data without manual adjustments. The presenter also shares a trick for organizing sheets between 'start' and 'end' markers to maintain a dynamic reference, making the process efficient and error-free.

Takeaways

• 📊 The script discusses an efficient method for combining budget data from multiple sheets in Excel using the VSTACK formula.
• 🔢 The VSTACK formula is used to vertically stack data from a range of cells across multiple sheets, starting from the first cell to the last in the specified range.
• 🗂️ The script demonstrates how to select a range of cells (A2 to D26) across sheets named from 'Jan' to 'June' using 3D references.
• 🔄 To make the VSTACK formula dynamic, additional 'start' and 'end' sheets are introduced to easily adjust the range of sheets included in the stack.
• 📋 The 'end' sheet is moved to the end of the workbook, and the formula is adjusted to reference from 'start' to 'end', making it easy to include new budget sheets.
• 🔄 When a new month's budget sheet is added, it can be moved to the end of the workbook, and the VSTACK formula is re-entered to include the new data.
• 🚫 The script also covers how to remove zeros from the consolidated data using the FILTER function.
• 📝 The FILTER function is applied to exclude rows where the first column (cost center ID) is zero, ensuring only relevant data is displayed.
• 🔧 The script suggests using the LET function to create a variable for all data, which can then be filtered to exclude zeros.
• 📈 The technique allows for easy updates and maintenance of budget data, as adding a new month's data and adjusting the formula is straightforward.
• 👍 The presenter invites viewers to share their thoughts on the technique and offers additional resources for learning more about the FILTER function.

Q & A

What is the main purpose of the budget spreadsheet discussed in the script?

The budget spreadsheet is used to track and compare actual and budget information for each month, with each month's data typically on a separate sheet.

What is the 'vstack' formula and how is it used in the script?

The 'vstack' formula is a new Excel formula used for vertically stacking data. It is used to combine the budget data from multiple sheets into one by selecting a range of cells on the first sheet and extending the selection to the last sheet.

What are 3D references in Excel, as mentioned in the script?

3D references in Excel refer to a way of referencing a range of cells that spans multiple sheets. In the script, the reference changes from a single sheet to a range of sheets (e.g., 'Jan to June') when using the 'vstack' formula.

How can the 'vstack' formula be made dynamic to include new sheets?

To make the 'vstack' formula dynamic, additional 'start' and 'end' sheets are added. The 'end' sheet is moved to the end of the workbook, and the formula is adjusted to reference from the 'start' to the 'end' sheet, allowing it to automatically include new sheets added in between.

What is the purpose of adding 'start' and 'end' sheets in the workbook?

The 'start' and 'end' sheets serve as markers to define the range of sheets that the 'vstack' formula should include. By keeping budget sheets between these markers and adjusting the formula accordingly, the data consolidation becomes dynamic.

How can zeros be removed from the consolidated data using Excel functions?

Zeros can be removed by using the 'filter' function in combination with the 'let' function. A variable is created to hold the consolidated data, and the 'filter' function is applied to exclude rows where the first column (e.g., cost center ID) is zero.

What is the benefit of using the 'filter' function to remove zeros from the data?

Using the 'filter' function to remove zeros helps in cleaning the consolidated data, making it more readable and relevant by excluding unnecessary rows that do not contribute to the analysis.

How does adding a new month's data affect the 'vstack' formula?

Initially, adding a new month's data does not automatically include it in the 'vstack' formula's result. The user needs to move the new month's sheet between the 'start' and 'end' sheets and then update the formula to include the new data.

What is the significance of the script's mention of a video on the 'filter' function?

The mention of a video on the 'filter' function suggests that there is additional educational content available for those who want to learn more about using this function in Excel for data manipulation and analysis.

How does the script demonstrate the flexibility and efficiency of using Excel formulas for data consolidation?

The script demonstrates the flexibility by showing how to dynamically adjust the 'vstack' formula to include new data without manual copying and pasting. It shows efficiency by automating the process of data consolidation and cleaning, which would otherwise be time-consuming if done manually.

Outlines

00:00

📊 Efficient Data Consolidation with Excel's VSTACK Formula

The speaker introduces a streamlined method for consolidating budget data across multiple sheets in Excel using the VSTACK formula. Traditional methods like manual copy-pasting or Power Query are replaced with a simple formula that vertically stacks data. The process involves selecting the first and last cells of the data range on the first sheet, extending the selection to the last sheet in the series, and utilizing 3D references to dynamically include all sheets between. The speaker also demonstrates how to make the formula dynamic by adding 'start' and 'end' sheets to easily adjust the range as new budget sheets are added.

🔄 Dynamic Formula Adjustment with Excel's Start and End Sheets

The speaker explains how to make the VSTACK formula dynamic by adding 'start' and 'end' sheets to the workbook. By placing all budget sheets between these two markers and adjusting the formula to reference from 'start' to 'end', the consolidation automatically includes new data as it's added. The speaker also shows how to handle the addition of a new month's budget by simply moving the new sheet to the end and updating the formula range to include all data from 'start' to the new 'end'.

🗑 Removing Zeros from Consolidated Data Using FILTER Function

The speaker demonstrates how to clean up the consolidated budget data by removing unnecessary zeros using the FILTER function. A variable named 'allData' is created to store the output of the VSTACK formula. The FILTER function is then applied to 'allData' to exclude rows where the first column (presumably cost center IDs) is zero, ensuring that only relevant data is displayed. The speaker emphasizes the ease with which this process can be repeated for new data, showcasing the flexibility and efficiency of the method.

📈 Demonstrating the Automatic Update of Consolidated Data

The speaker illustrates the automatic update feature of the consolidated data by adding a new month, August, to the budget. Initially, the new data does not appear in the consolidated view because it's not between the 'start' and 'end' sheets. However, by moving August into position and adjusting the formula to reflect the new range from 'start' to 'end', the data is seamlessly included in the report. The speaker also invites viewers to share their thoughts and offers additional resources for learning more about the FILTER function.

Keywords

💡Budget Spreadsheet

A budget spreadsheet is a financial tool used to track and manage income and expenses against a set budget. In the video's context, it is used to compare actual spending with budgeted amounts on a monthly basis, with each month represented on a separate sheet. This is essential for financial planning and analysis.

💡Actual and Budget Information

This refers to the recorded financial data of what has actually been spent (actual) and what was planned to be spent (budget). The video discusses how to combine these two sets of data for a comprehensive view of financial performance.

💡VSTACK Formula

The VSTACK formula is a function in Excel used for vertically stacking data from multiple sheets. In the video, it is introduced as a simple and efficient method to consolidate budget data from various sheets into one, making it a key concept for automating the data combination process.

💡3D References

3D references in Excel allow formulas to span across multiple sheets. The video explains how to use 3D references to select a range of cells from the first to the last sheet dynamically, which is crucial for the VSTACK formula to work across all budget sheets.

💡Dynamic Formula

A dynamic formula in Excel adjusts automatically to changes in the workbook, such as the addition of new sheets. The video demonstrates how to make the VSTACK formula dynamic by referencing 'start' and 'end' sheets, allowing the formula to include all budget sheets in between.

💡Start and End Sheets

These are additional sheets added to the workbook to serve as markers for the beginning and end of the budget data. In the script, the 'start' and 'end' sheets are used to make the VSTACK formula dynamic, ensuring that all budget sheets are included regardless of their number or order.

💡Filter Function

The filter function in Excel is used to remove or include data based on certain criteria. The video shows how to use the filter function to exclude rows with zeros in the first column, which represent rows without a cost center ID, thus refining the consolidated data.

💡LET Function

The LET function in Excel allows the creation of named variables within a formula, making complex formulas easier to read and manage. In the video, LET is used to define 'allData', which stores the result of the VSTACK formula before applying the filter function.

💡Choose Columns

This is a function used within the filter function to select specific columns from a dataset. In the context of the video, 'choose columns' is used to target the first column of the data for the filter condition, checking for non-zero values to exclude rows with no cost center ID.

💡Consolidated Data

Consolidated data refers to the combined data from multiple sources into a single, unified dataset. The video demonstrates the process of creating consolidated budget data by vertically stacking and filtering the data from multiple sheets, resulting in a comprehensive view of the budget.

Highlights

Introduction of a simple Excel formula to combine budget data from multiple sheets.

Use of the 'vstack' formula for vertically stacking data in Excel.

Explanation of how to select a range of cells for the vstack formula.

Concept of 3D references in Excel for dynamic sheet referencing.

Combining entire budget data with a single formula entry.

Adding new budget sheets and updating the vstack formula dynamically.

Creating 'start' and 'end' sheets for easier management of budget sheets.

Making the vstack formula dynamic by referencing 'start' to 'end'.

Removing zeros from consolidated data using the 'filter' function.

Using the 'let' function to create a variable for all data.

Filtering out rows with zero in the first column to clean the data.

Automatic update of the consolidated report with new data.

Demonstration of the dynamic nature of the Excel formula with the addition of August data.

Engagement with the audience to share thoughts on the technique.

Invitation to watch a video for further understanding of the filter function.

Closing remarks and sign-off.

Transcripts

so we have this budget spreadsheet that

has the actual and budget information

for every month one per sheet normally

to combine this kind of a data I would

either manually copy paste things or use

power query but let me tell you a secret

I haven't been using power query or the

manual methods to do this that's right

I'm using a really simple formula to

solve this problem and that is the

vstack formula it is a new formula added

to Excel for vertically stacking the

data so we write v stack Open Bracket

and then go to the very first month

select the first cell and the last cell

by holding the shift so here I'm picking

A2 to d26 range and then hold the shift

and select the last sheet now my sheet

reference is going to change to Jan to

June and then the range reference

becomes A2 to d26 these kind of

references are called called 3D

references that's right and when you

close the bracket and hit enter we are

going to get the entire budget data

combined here in one go I mean how

awesome is this now let me share a few

more tricks with you let's say if I want

to add July budget I can go here add a

sheet and then name it as July for the

sake of Simplicity I'm going to copy

April values and paste it into July now

you can see that even after adding the

July data it's not added here in fact

the reference still says Jan through

June so to make the whole vstack formula

Dynamic here is a really simple trick

add one more sheet and call this as

start then add another sheet and then

name this as end and now move the end

sheet all the way to the end to make it

easy for us to spot I'm going to color

these in this Orange color here so now

every time I use the workbook I want to

keep my budget sheets in between the

start and end then I'm going to take my

July sheet and move it to the end here

and come back to my vstack formula

delete it and this time I'm going to say

v stack and as our data goes from A2 to

d26 we'll just select it from Jan again

and go all the way to the end so it

becomes Jan to end and replace the word

Jan with start so we saying go from

start to end and get everything from A2

to

d26 when you hit enter you're going to

get this report again with all the data

but now what about these zeros we can

easily remove the zeros using a function

like filter so to remove the zeros we

can go back to the vack formula and then

use a function like let and create a

variable like allore data and keep the

vstack value into that all data now that

we have the all data variable I can use

the filter function filter all data and

then I want to check the very first

column of all data and if it is zero

that means we don't have a cost center

ID and we can ignore that so we can say

choose Columns of all data comma 1

that's going to pick the very first

column so these zeros and CX 216 Etc

is not equal to zero that's it when you

close and hit enter you're going to get

just the Consolidated data without those

zeros the beautiful thing with this is

if you add a new month so for example

let's bring in August now and again this

time I'm going to be lazy and copy the

May data for August paste it here right

now the August data is not going to be

here because it's not between start and

end let's make a note of how many rows

is there we're going from row number two

all the way up to

176 let's see what happens if I take

this August and move it in between start

and end those two colored

Shades and when I go back here my data

goes up to row number

2011 again just awesome really what do

you think about this technique let me

know in the comments and if you do want

to learn a bit more about this filter

function I've got a Beautiful video on

the channel for you check it out it

shows up on the screen I'll catch you

there bye