Tutorial: Building a Customer Engagement and Sales Metrics Report in Excel

Welcome to Other Levels. In today's tutorial, we're continuing our journey in building a customer engagement and sales metrics report using Microsoft Excel. Make sure to visit other-levels.com to download the templates and data sets used in this tutorial.

Steps to Follow

1. Table Headers:

   • Add table headers in different colors and font sizes.

2. Dynamic Table:

   • Create a dynamic table using several pivot tables and formulas.

3. First Pivot Table:

   • Add the customer to the row field and the amount to the values field.
   • Sort the customer list based on the amount from largest to smallest using the data menu.

4. Link Cells:

   • Go to the report sheet and link the cells to the pivot table.
   • Show customer's name and sales amounts.

5. Error Handling:

   • Add the IFERROR formula to avoid displaying any errors.
   • Add numbering and drag to get the total sales.

6. Contribution to Sales Percentage:

   • Create a new pivot table for the amount in the values field.
   • Change the sum of the amount to the percentage of the column total using the VLOOKUP formula.

7. Growth Sales:

   • Duplicate the pivot table, add year in the column field.
   • Use "percentage difference from" for the base item as previous year with the VLOOKUP formula.

8. Total Orders and Offers:

   • Calculate successful rate by dividing the orders with the offers.
   • Make several adjustments for the look and feel.

9. Sparklines:

   • Add sparklines based on the three years of sales.

10. Filters:

    • Insert filters for customers and yearly data.

Conclusion

That's it for this video. Hope you've learned something useful for your career. Visit our website, other-levels.com, to download the templates and data sets. Thank you, and see you in the next video.