Combine Excel Sheets with *this* simple formula
Summary
TLDRThis 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
📊 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.
Mindmap
Keywords
💡Budget Spreadsheet
💡Actual and Budget Information
💡VSTACK Formula
💡3D References
💡Dynamic Formula
💡Start and End Sheets
💡Filter Function
💡LET Function
💡Choose Columns
💡Consolidated Data
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
Weitere ähnliche Videos ansehen
Scraping with Google Sheets
Filter Views - How to Filter Google Sheets Without Affecting Other Users
Tech Tips #66 - Automatically Continuously Sort Data in Google Sheets
Curso de Excel 2023 - Aula de Apoio da Aula 1 [Resolvendo caso da Função SE Aninhada no Excel]
LinkedIn Data Scraping Tutorial | 1-Click To Save to Sheets
Use GET.WORKBOOK to List Worksheet Names without VBA
5.0 / 5 (0 votes)