Consolidar datos o unir diferentes libros y hojas en Excel - Dostin Hurtado

How to Create a Simple Data Consolidation in Excel

Introduction to Data Consolidation

• The speaker introduces the tutorial, emphasizing the simplicity of data consolidation in Excel and encourages viewers not to skip ahead for better understanding.

Understanding the Files

• The speaker mentions having two files for January and February, indicating that users may typically work with multiple months or even years for comparisons.

• It is noted that while this example uses files, users can also consolidate data from different sheets within a single workbook.

Requirements for Consolidation

• The speaker explains that Excel requires tables being consolidated to be identical in structure, highlighting the importance of consistent formatting across datasets.

• Emphasis is placed on consolidating quantitative data (numbers), as qualitative data (descriptions) are less frequently required for such tasks.

Creating a New File

• Instructions are given on how to create a new file using either keyboard shortcuts or menu options in Excel.

Using the Consolidate Function

• The tutorial moves into using the "Consolidate" function, starting with summing values as it is commonly requested by businesses.

• The speaker demonstrates selecting data from another file (January), explaining how to add references correctly.

Selecting Data Ranges

• A detailed explanation follows about selecting ranges from both January and February files while addressing common issues encountered during selection.

Configuring Consolidation Settings

• The importance of setting up row and column labels correctly is discussed; specifically, how these settings affect the organization of consolidated data.

Linking Data Between Files

• The concept of creating links between the new consolidated file and original datasets is introduced. This ensures updates in source files reflect automatically in the consolidated view.

Reviewing Consolidated Results

• Upon reviewing results, it’s noted that some aggregated figures may appear irrelevant or unhelpful without proper context or breakdown by month.

Conclusion on Relevance of Data

Consolidating Data in Excel

Introduction to Data Consolidation

• The speaker discusses the challenges of using a consolidated data table that presents irrelevant information, prompting the need for a new approach.

• Emphasizes the importance of dynamic tables in Excel, suggesting that users should avoid manually altering data and instead utilize built-in features.

Creating Dynamic Tables

• Demonstrates how to insert a pivot table in Excel, selecting relevant columns such as "cost per consultation," "medication payment," and "total to pay."

• Reiterates the process for February, ensuring consistency by inserting another pivot table with similar parameters.

Steps for Data Consolidation

• Outlines the steps to consolidate data from January and February while ensuring proper selection of headers and left columns.

• Advises against including total figures in the consolidation since they will be calculated automatically; emphasizes correct selection methods.

Analyzing Consolidated Data

• Shows an example where consolidated data reveals insights into consultations and payments across different months.

• Discusses how to manage multiple months' data effectively without cluttering or deleting essential information.

Visualizing Data Insights

• Suggests creating bar charts from consolidated data to visualize income sources effectively, highlighting key contributors like Bogotá and Medellín.

• Concludes with encouragement on utilizing learned skills for effective data management, emphasizing simplicity in creating dynamic tables.

Final Thoughts on Knowledge Sharing