Crea este Dashboard desde cero completamente en Excel ¡Impresiona a tu jefe!

Introduction to the Dashboard Tutorial

Overview of the Dashboard

• The tutorial is presented by Andrés Rojas Moncada, focusing on building a sales dashboard from scratch. The video includes a downloadable link for resources to facilitate learning.

Key Features of the Dashboard

• The dashboard will display relevant information about sales force performance in retail environments, with indicators such as total sales and monthly breakdowns. This can be adapted to various work scenarios.

• It includes metrics like total revenue, highest and lowest sales months, and average monthly sales figures. Additionally, it features a card showing total revenue and purchase volume within the establishment.

Performance Indicators

Employee Performance Metrics

• Key performance indicators (KPIs) for employees are included, tracking customer interactions per cashier and unique customers to calculate purchase frequency indices over an annual period. This helps assess employee efficiency.

• The dashboard also visualizes customer wait times at checkout and categorizes payment methods used by customers through percentage breakdowns displayed in circular graphs.

Interactive Features

User Interaction with Data

• Users can segment data by cashier using slicers that allow selection of individual or multiple employees to view specific performance metrics related to those selections. This enhances data analysis capabilities for users.

Visual Enhancements

• A macro will be created using Visual Basic for Applications (VBA) to add shading effects on cards within the dashboard, improving clarity and presentation of information visually through color coding (violet, green, pink, blue).

Data Preparation Steps

Setting Up Data Source

• The data source consists of approximately 11 columns detailing transaction specifics such as cashier ID, invoice opening/closing times, worker shifts, day/week/month identifiers, client codes, payment methods used by clients, channel descriptions, and totals across roughly 105k rows of data. It's crucial to convert this into a table format for Power Pivot processing.

Activating Power Pivot

• Instructions are provided on activating Power Pivot within Excel's developer tab to manage data models effectively before creating calculated columns based on invoice dates that determine weekdays numerically for sorting purposes later in the analysis process.

This structured approach ensures clarity while navigating through key concepts discussed in the tutorial video regarding building an effective sales dashboard using Excel tools.

Creating a Dynamic Dashboard in Excel

Initial Setup and Measures

• The process begins with the creation of a measure to sum the total billed amount, using the SUM function. This measure is named "Suma de Total."

• The next step involves formatting this measure for use in a pivot table, where various elements will be added to visualize data effectively.

Designing the Dashboard Layout

• A summary sheet is created to house all pivot tables and dashboard elements, starting with a colored square background instead of plain white for aesthetic appeal.

• The color is adjusted quickly using hexadecimal codes from a color palette, specifically choosing an off-white or "hueso" color for better visual contrast.

Organizing Elements

• Naming the background layer helps maintain organization when programming macros, allowing easy identification of each dashboard component.

• A rounded-corner shape is used to display the company logo and dashboard title, emphasizing design consistency.

Formatting Text and Fonts

• It’s important to remove backgrounds from text boxes and shapes that contain graphics or icons to avoid interference with shadows during macro programming.

• The font "Open Sans" is recommended for its readability on digital screens; it can be downloaded from Google Fonts if not pre-installed.

Creating Visual Data Representations

• After finalizing text elements, attention shifts to creating graphical representations of sales distribution by month as part of the video tutorial.

• One segment filter will initially be implemented focusing on cashiers' productivity metrics within retail environments.

Utilizing Power Pivot for Data Analysis

• To insert pivot tables correctly, users must utilize Power Pivot rather than traditional methods found under the Insert tab; this ensures proper communication between data models.

• The first pivot table is created using invoice opening dates as row fields; however, errors may arise due to excessive date entries exceeding Excel's capacity.

Final Adjustments and Insights

Creating Dynamic Graphs with Excel

Steps to Create a Dynamic Graph

• The speaker discusses the importance of using dynamic fields in Excel to enhance the visual appeal of graphs, specifically by utilizing monthly data and total sums for innovative graph designs.

• A quick method is demonstrated for copying month elements to create formulas that calculate averages, minimums, and maximums from a pivot table, which will serve as the basis for the graph.

• The speaker explains how to reference static fields dynamically by importing data directly into Excel, allowing for real-time updates in graphs when underlying data changes.

• Filtering techniques are introduced using Office 365 functions to manage datasets that may have missing values for certain months, ensuring only relevant data is included in calculations.

• The process of creating a small pivot table is outlined to extract minimum and maximum sales figures along with average calculations through specific formulas like INDEX and MATCH.

Utilizing Functions for Data Analysis

• The speaker emphasizes the use of INDEX and MATCH functions to find specific elements within a dataset efficiently, particularly focusing on identifying months with the least and most sales.

• An explanation follows on how to format results so that returned month names start with capital letters while maintaining clarity in data presentation.

• The calculation of minimum sales figures is detailed, showcasing how these can be linked back to their respective months using formulae effectively.

• Similar steps are reiterated for determining maximum sales figures while ensuring proper formatting conventions are followed throughout the analysis process.

Finalizing Graphical Representation

• To visualize contrasts between minimum and maximum sales alongside average values, a straightforward approach using an average function is recommended.

• Reference cells are utilized strategically within formulas to ensure accurate representation of processed monthly data without leaving empty spaces in graphs.

• A segmenting feature is introduced where existing styles can be duplicated and customized visually through color changes and formatting adjustments for better aesthetics.

• Detailed instructions on modifying segment borders and colors highlight the importance of visual consistency across dashboard elements for effective communication of information.

Dynamic Graph Creation and Customization

Selecting Data and Initial Setup

• The process begins with selecting all labels from January to December, summing totals 1 and 2, and creating a dynamic line graph featuring multiple lines on the same axis.

• A transformation is applied by removing dynamic chart buttons and graphical elements to present a clean line representation for each month.

Customizing Graph Appearance

• The type of graph is adjusted: total 1 remains a line while total 2 is represented as a polygon, allowing for color shading effects.

• A gradient fill in pink is added beneath the curve of total 2, enhancing visual appeal while adjusting line thickness to 1.5 points with smoothing options enabled.

Enhancing Visual Elements

• Two gradient fills are positioned at opposite corners of the graph; one corner has full transparency while the other has partial transparency (40%-50%) to create depth.

• Emphasis on positioning elements correctly within the graphic ensures clarity without overwhelming background colors.

Final Adjustments and Formatting

• Adjustments are made to reduce the intensity of the pink line for better visibility against a white background, ensuring it does not clash visually.

• Techniques learned through practice help refine visual elements that effectively communicate data insights.

Labeling and Additional Information

• Labels are placed above lines with specific formatting for values over 1000, using bold gray text for clarity.

• Titles such as "Total Facturado por Meses" are created to describe overall data trends clearly, alongside additional cards displaying maximum, minimum, and average figures.

Streamlining Workflow

• Copying existing graphic elements speeds up workflow; adjustments can be made easily without starting from scratch.

Creating Visual Elements in Excel Dashboards

Adding Default Images and Icons

• The process begins with loading a default visual element, specifically an image that allows for multiple selections. An icon resembling an arrow is added, rotated 90 degrees, and colored green to signify higher sales.

Adjusting Aspect Ratios

• It's important to lock the aspect ratio when modifying dimensions; here, the height is set to 0.3 inches and width to 0.4 inches for better visual comfort.

Creating Sales Indicators

• The speaker quickly creates visuals for both highest and lowest sales indicators to streamline the dashboard creation process while ensuring clarity in data representation.

Icon Availability and Sourcing

• Icons are available from Excel versions 2016 onward, including Office 365. If not accessible directly, users can download icons from various websites without issues.

Enhancing Graphic Placement Precision

• For precise placement of small graphic elements, users can either zoom in on Excel or use keyboard arrow keys for accurate adjustments. Grouping elements within cards is recommended for effective shadow control later on.

Creating New Measures in Data Tables

• A new measure is created using a function called COUNTROWS, which counts all rows in a data table. This helps format numbers correctly with thousand separators for better readability.

Inserting Dynamic Tables

• A new pivot table is inserted into the summary sheet immediately after naming it. Naming tables during creation aids in managing connections with slicers effectively.

Total Sales Calculation

• The dynamic table calculates total sales based on data entries representing customer purchases—specifically noting over 105,000 transactions recorded.

Utilizing Existing Pivot Table Elements

• Users are encouraged to leverage existing pivot tables instead of creating new ones if they serve similar purposes, enhancing efficiency in dashboard design.

Designing Financial Summary Cards

• A card displaying total invoicing figures for selected periods (e.g., year 2022) is created using an icon representing currency bills colored gold for emphasis.

Tips for Effective Dashboard Creation

• Key tips include customizing visual elements according to personal preferences while maintaining clarity and functionality within the dashboard layout.

Accessing Templates

Dashboard Creation and Data Analysis in Excel

Creating a Horizontal Divider for Reporting

• The speaker discusses adding a horizontal line to visually separate the total billed from the total purchases or dispatched orders. This helps in organizing data effectively.

Dynamic Table Adjustments Based on Staff Availability

• A dynamic table is utilized to calculate totals based on selected months, accommodating staff absences due to vacations or days off. This ensures accurate reporting of sales figures.

Enhancing Dashboard Descriptiveness

• Emphasis is placed on making the dashboard as descriptive as possible, showcasing both total billed amounts and purchase volumes for dispatched products. This clarity aids user understanding.

Importance of Subscription and Community Engagement

• The speaker encourages viewers to subscribe, highlighting that approximately 237 videos have been uploaded throughout the year to enhance community learning about Excel skills. This fosters a sense of community among learners.

Connecting Slider Controls with Dynamic Tables

• It’s crucial to connect slider controls with dynamic tables that feed into visual elements like cards, ensuring real-time updates reflect changes made by users interacting with the dashboard.

Visual Elements for Enhanced User Experience

• The addition of icons (like shopping carts) and adjusting their sizes improves visual appeal and usability within the dashboard layout, making it more engaging for users.

Aligning and Distributing Dashboard Elements

• Techniques are shared for aligning shapes and distributing them evenly across the dashboard, which enhances aesthetic consistency and organization within the report layout.

Inserting New Dynamic Tables for Sales Analysis

• A new dynamic table is introduced focusing on top sales channels, specifically electronic channels, allowing users to analyze payment methods used in transactions effectively.

Filtering Data for Relevant Insights

• The process includes filtering data within dynamic tables to display only electronic payment methods, streamlining information retrieval related to sales performance metrics.

This structured approach provides an organized overview of key concepts discussed in creating an effective Excel dashboard while emphasizing user engagement through clear visuals and interactive elements.

Dynamic Data Representation in Dashboards

Utilizing Dynamic Elements for Data Updates

• The discussion begins with the importance of using dynamic elements in dashboards, specifically replacing static text (e.g., "colpatria") with cell references (like R12) to ensure that data updates automatically when the pivot table changes.

Creating a Top 5 Sales Card

• A card is being designed to display the top 5 electronic sales channels, highlighting which banks contribute most to total sales. This visual representation aims to simplify understanding of sales performance across different channels.

Displaying Key Metrics Effectively

• The focus is on showing total sales without cluttering the card with unnecessary details like percentage participation, which could overwhelm viewers. Instead, it emphasizes clarity by presenting only essential information.

Aligning Visual Elements for Clarity

• The speaker discusses aligning visual elements within the dashboard for aesthetic consistency and ease of interpretation. Proper alignment enhances readability and ensures that all components are visually coherent.

Differentiating Between Day and Night Sales Channels

• There’s an exploration of how different electronic channels perform during various times of day, suggesting a need for metrics that reflect customer preferences based on time-specific data.

Tips for Efficient Element Distribution

• A technique is shared for quickly aligning multiple visual elements: select the top and bottom items first, then distribute all intermediate items either vertically or horizontally to maintain uniform spacing without manual adjustments.

Maintaining Formatting During Updates

• When updating formulas linked to shapes or visuals, formatting can be lost. A quick fix involves double-clicking the format painter tool to apply consistent formatting across selected elements efficiently.

Final Adjustments and Grouping Elements

Creating Dynamic Dashboards in Excel

Designing the Dashboard Layout

• The speaker demonstrates how to select and align elements on a dashboard, ensuring that all components have uniform positioning for visual consistency.

• A summary of the data presented includes total sales by month, highlighting both peak and low sales periods, as well as average sales figures and total invoiced amounts.

• The speaker mentions testing the created cards and anticipates sharing additional information in a follow-up video to enhance understanding of the dashboard's functionality.

• Emphasis is placed on mastering the tools necessary for creating dashboards that impress viewers, whether they are colleagues or supervisors in various settings.