Summary
Highlights
Data is essential for decision-making in companies, transforming the labor market. The World Economic Forum highlights business intelligence as a growing trend. Despite the demand for data analysis skills, 61% of companies identify a lack of these competencies in professionals. Microsoft Excel is the leading data processing program, used by over 500 million people, and advanced Excel skills are highly valued in all sectors. Carolina de Andrade aims to break the paradigm that Excel is difficult, attributing difficulties to poor teaching methods. She emphasizes that her courses are practical and address real-world business challenges, boasting over 30,000 students in more than 60 countries. She mentions her recognition by Microsoft as an MVP in Excel and her YouTube channel as one of the five largest Excel channels globally. The Excel marathon is designed to share this methodology and empower Latin American talent, encouraging viewers to seize the opportunity to master Excel for professional and personal growth.
Carolina de Andrade begins the first class by asking if a typical Excel layout with borders is an actual Excel table. She clarifies that these are "pseudotables" or "cells with borders" and not true Excel tables, which offer more functionality. While pseudotables display data, they have significant limitations. For instance, when filtering data in a pseudotable, the calculated totals might disappear or provide incorrect information because Excel doesn't recognize the data as structured totals. In contrast, true Excel tables (created using "Format as Table" in the Home tab) automatically apply a "Total Row" feature that dynamically updates subtotals based on filters. This feature allows for various calculations like sums, averages, and counts, simplifying data analysis.
Carolina highlights the advantages of structured Excel tables. When adding a new column to a structured table, such as an 'IVA' (VAT) column, Excel automatically extends the table's format and applies formulas to all new rows, saving significant time and effort. Unlike pseudotables, where users would have to manually format, drag formulas, and update totals, structured tables automate these processes. She emphasizes that formulas in structured tables are more intuitive, using descriptive names like "[Monto]*7%" instead of cell references like "E7*7%," making them easier to read and debug. This automation not only speeds up work but also reduces human error, ensuring data consistency and accuracy.
Carolina emphatically states that combining cells within data ranges is a major error in Excel. While combining cells might seem aesthetically pleasing, it severely compromises data integrity and analytical capabilities. When cells are combined, Excel only retains the value of the top-left cell and discards the rest. This creates issues when filtering or performing operations, as Excel only sees the value in the combined cell, leaving other cells in the range effectively blank. As a result, filters might not work correctly, leading to incomplete or inaccurate data displays. Structured Excel tables, by design, prevent users from combining cells within the data range, reinforcing best practices for data organization. Combining cells is only acceptable for titles or subtitles outside the primary data area.
Carolina explains how to convert existing pseudotables into proper structured Excel tables, emphasizing that this transformation doesn't require restarting from scratch. Users can select their pseudotable, go to the 'Home' tab, choose 'Format as Table,' and then right-click on the desired style to select 'Apply and Clear Formatting.' This action cleans up existing formatting and applies the new structured table properties, ensuring consistency and full functionality. She also introduces data segmentation (slicers) as a powerful filtering tool, accessible through the 'Table Design' tab. Slicers allow users to filter data using interactive buttons, providing a more visual and intuitive experience than traditional filters. She demonstrates how to insert and customize slicers, including arranging buttons horizontally and applying different color schemes. This feature, often mistaken as exclusive to pivot tables or requiring macros, is available directly for structured tables, greatly enhancing data interaction and presentation.
Carolina clarifies the difference between structured tables and pivot tables. She explains that pivot tables are used for summarizing, counting, and totalizing large datasets. The term "dynamic" refers to their ability to quickly rearrange and display data in different configurations. She demonstrates how to create a pivot table by selecting the data source (a structured table, emphasizing the benefits of using one) and choosing where to place the pivot table. Once created, users can drag fields (columns) into areas like 'Rows,' 'Columns,' and 'Values' to quickly generate summaries. For example, she shows how to summarize expenses by collaborator and type, illustrating how quickly the layout can be changed by dragging fields. This dynamic nature allows for rapid analysis of different data perspectives without manual recalculations, saving time and reducing errors.
Carolina emphasizes that pivot tables need to be manually refreshed to reflect any changes in the source data. This includes adding, modifying, or deleting rows in the original structured table. She explains that while future Excel versions might offer automatic updates, currently, users must right-click on the pivot table and select 'Refresh' (or use Alt+F5). She highlights another significant advantage of structured tables: automatic formula propagation. When a new column with a formula (like IVA or Total) is added, the formula automatically applies to all existing and new rows. This ensures consistency and saves effort compared to manually dragging formulas in traditional cell ranges. This capability, combined with pivot tables, offers a robust and efficient data management and analysis workflow.
Carolina stresses that the effectiveness of Excel tools like pivot tables hinges on proper data structure. Many common problems arise because data is poorly organized. She debunks several common but incorrect data organization practices: storing data in multiple separate files or sheets based on categories (e.g., a file per salesperson, a sheet per month). She emphasizes that all related data, especially if it shares the same columns, should be consolidated into a single, comprehensive structured table on one sheet. This single-source approach ensures that all data is accessible for unified analysis and prevents issues where tools like pivot tables can't access all relevant information, leading to incomplete reports. She illustrates this by showing how aggregating sales data from different sheets into one structured table allows for comprehensive analysis by salesperson, month, and brand.
Carolina introduces Excel's 'Flash Fill' feature, an AI-powered tool that automatically recognizes patterns and extracts or separates data. She demonstrates this by cleaning a messy 'Product Description' column that contains SKU, brand, and product name in a single cell. Instead of manually separating these values, she types the first few entries for 'Brand,' 'Product,' and 'SKU' into new columns. Excel's Flash Fill (found in the 'Data' tab) then intelligently completes the rest of the column, extracting the corresponding information based on the pattern provided. This feature, available since Excel 2013/2016, saves immense time and effort in data preparation. She then shows how, with the data now properly structured into separate columns, pivot tables can be easily created to summarize sales by brand and product, an impossible task with the original messy data.
Carolina illustrates the power of AI, specifically ChatGPT, for large-scale data consolidation. She shows how she used ChatGPT to combine multiple Excel sheets, each representing sales data from different periods or salespeople, into a single consolidated table. She explains that simply uploading the multi-sheet Excel file to ChatGPT and providing natural language instructions (e.g., "consolidate all data from different sheets, add a 'Salesperson' column based on cell G6 from each sheet") results in a unified table. This process, which would be labor-intensive manually, is dramatically accelerated by AI. She also highlights the importance of discerning when to use AI and when to use Excel's native features. While AI excels at complex consolidation, simpler tasks (like Flash Fill) are faster directly in Excel. She also demonstrates how to troubleshoot common date-related issues in pivot tables. If dates don't group correctly by month, it's often due to inconsistent date formats or non-date entries in the source column. She advises ensuring all entries are valid dates and offers a quick tip (Ctrl+Shift+#) to format dates correctly. By maintaining clean, structured data, users can unlock the full potential of both Excel and AI, seamlessly integrating these tools for efficient data processing and robust reporting.