Tablas Dinámicas en Excel 2010: Filtrar Datos. Segmentación de Datos o Slicers.

Excel Dynamic Tables: Filtering Options

Introduction to Dynamic Tables

• The session focuses on advanced filtering options for dynamic tables in Excel, building on previous lessons that covered basic creation and field distribution.

• Introduction of a new tool in Excel 2010 called "Slicers" (segmentación de datos), which enhances filtering capabilities beyond traditional report filter fields.

Using Report Filter Fields

• Explanation of how to use report filter fields to filter content within dynamic tables, noting some limitations that can be overcome with Slicers.

• The document used for the session contains 250,000 rows of data, reduced from one million for easier downloading and handling.

Creating a Dynamic Table

• Steps are outlined for creating a dynamic table using income data from various bank branches, including year, month, branch, account type, customer status, and amount.

• Emphasis on selecting the correct data range when inserting a dynamic table; users should ensure all relevant rows are included.

Configuring the Dynamic Table

• Instructions provided on dragging fields into different areas of the table: values (amount), rows (account type), and columns (branch).

• Formatting numeric data as currency is demonstrated to enhance clarity in financial reporting.

Implementing Filters

• Discussion on adding the year field to the report filter area allows users to view income information by specific years or multiple years.

• Demonstration of how selecting specific years filters the displayed information accordingly; visual cues indicate active filters.

Advanced Filtering Techniques

• Users can select multiple years by activating "Select Multiple Items," allowing for more complex queries across different time frames.

• Instructions provided for removing filters to revert back to viewing all available data; users can also choose "All" from the dropdown menu.

Excel Filtering Techniques

Overview of Filtering Options

• The process to clear filters in Excel involves navigating to the options tab, selecting the delete button, and choosing "clear filters" to view all data again.

• Users can organize information in a pivot table by dragging multiple fields into the filter area, allowing for more complex filtering such as by year and month simultaneously.

Advanced Filtering Capabilities

• It is possible to filter data first by year (e.g., 2012) and then further refine it by specific months (e.g., January to March).

• Users can also aggregate data across multiple years while maintaining clarity in their analysis, showcasing Excel's efficiency in handling large datasets.

Limitations of Standard Filters

• While using standard filters allows for selection of multiple years, it may lead to confusion regarding which years' data is currently being viewed.

• Selecting multiple elements in filters does not provide clarity on which specific items are selected, potentially complicating data interpretation.

Introduction of Data Segmentation Tools

• Excel 2010 introduced "Slicers," or segmentación de datos, which enhance filtering capabilities by providing visual controls that display applied filters clearly.

• To use Slicers effectively, users must select a cell within the pivot table before accessing the option to insert them from the options tab.

Utilizing Slicers for Enhanced Data Analysis

• After inserting a Slicer for years, users can easily manipulate it without affecting other parts of the worksheet; resizing is straightforward.

• The Slicer visually indicates which years are currently filtered. Clicking on a year will instantly update the displayed data accordingly.

Multi-Year Selection with Slicers

• Users can select individual years or hold down the control key to choose multiple years simultaneously within the Slicer interface.

• This functionality allows users to maintain visibility over selected filter options while adjusting their criteria dynamically.

Dynamic Filtering in Excel Pivot Tables

Understanding Filters in Pivot Tables

• The speaker discusses the lack of clarity regarding applied filter options in a pivot table, noting that users must click on an arrow to view filtering options.

• A new slicer for months is introduced; the user positions it next to the pivot table and adjusts its size to accommodate many months.

• The process of selecting specific months (January to March) is demonstrated, including how to add additional selections using the Control key.

• The speaker emphasizes the convenience of slicers over report filter fields, as they visually display selected options without needing to navigate through dropdown menus.

• It is suggested that report filter fields may no longer be necessary due to the functionality provided by slicers.

Customizing Slicers

• Users can remove year and month filters from report fields while retaining slicers, allowing for flexible data manipulation.

• The speaker notes that slicers have various customization options and suggests differentiating them by color for better visual distinction.

• Instructions are given on changing slicer styles using predefined themes or creating custom styles with specific formatting elements like borders and fills.

• A quick demonstration shows how to create a simple custom style for a slicer header but concludes that it may not be aesthetically pleasing enough for saving.

• To delete a custom style, right-clicking on it prompts confirmation before removal.

Removing Slicers and Exploring Additional Filter Options

• The simplicity of removing slicers is highlighted; users can simply select and press delete to clear filters instantly.

• Upon removal of slicers, all previously filtered information reappears, restoring full visibility of data within the pivot table.

Advanced Filtering Techniques

• The discussion shifts towards filtering options available in row and column label areas within pivot tables, which also feature dropdown arrows for sorting data.

• Initial focus is placed on quickly filtering data by branch names; users can easily toggle visibility for specific branches like Barcelona or Madrid.

Dynamic Filtering Options in Data Tables

Introduction to Filtering Data

• The discussion begins with the introduction of filtering options available for accounts in branches located in Barcelona and Madrid, emphasizing the efficiency of obtaining results quickly through these filters.

Removing Filters

• Instructions are provided on how to remove applied filters. Users can click on a dropdown arrow and select "clear filter" to revert back to viewing all branches.

Advanced Filtering Techniques

• The speaker suggests that there are more advanced filtering options available beyond basic ones, hinting at the potential for complex data analysis.

Using Text-Based Filters

• A specific example is given about filtering account types by investment funds. The option "contains" allows users to search for partial text matches within account names.

Applying Value Filters

• The speaker explains how value filters can be used, such as showing only branches with deposits of 100 million or more. This involves selecting appropriate conditions from a dropdown menu.

Demonstrating Value Filter Conditions

• An example is provided where the user sets a condition to show branches that have deposited between 20 and 50 million, illustrating practical application of value filters.

Exploring Row Filters

• The discussion shifts towards applying filters on rows based on deposit amounts, specifically targeting those above 170 million. This showcases the flexibility of data manipulation within tables.

Conclusion and Future Topics