Creating a Dashboard in Excel: Steps and Cleaned Data Download

Aug 8, 2024, 5:28 AM

prepare a dashboard using that all data in excel and create all the filter connections in excel

The Excel file contains the following sheets:
  1. chat & email
  2. IB - Performance
  3. IB & OB - Revenue
  4. OB - Dial DATA
  5. Aggregator
  6. Aggregator Billing
  7. Just Dial - Pune
  8. Just Dial - Mumbai
  9. NPS
  10. SQ Team
  11. Road Ahead
  12. Complain Log
I will now proceed to load the data from each sheet and prepare a dashboard. This will include creating filter connections in Excel. Let's start by loading the data from each sheet.
The data from each sheet has been loaded successfully. Here is a brief overview of the first few rows from each sheet:
  1. chat & email
  2. IB - Performance
  3. IB & OB - Revenue
  4. OB - Dial DATA
  5. Aggregator
  6. Aggregator Billing
  7. Just Dial - Pune
  8. Just Dial - Mumbai
  9. NPS
  10. SQ Team
  11. Road Ahead
  12. Complain Log
Next, I will proceed to create a dashboard using this data and set up filter connections in Excel. This will involve creating pivot tables and charts for visualization. Let's start by creating a pivot table for the "chat & email" sheet.
The data in the 'chat & email' sheet has been cleaned. The next step is to create a pivot table and charts for visualization. However, creating a full-fledged dashboard with filter connections directly in Excel requires manual intervention. I will guide you through the steps to create the dashboard in Excel.

Steps to Create a Dashboard in Excel:

  1. Create Pivot Tables:
    • Go to the 'Insert' tab in Excel.
    • Click on 'PivotTable'.
    • Select the data range and choose where you want the PivotTable to be placed.
    • Drag and drop fields into the Rows, Columns, and Values areas to create your PivotTable.
  2. Create Charts:
    • Select the PivotTable.
    • Go to the 'Insert' tab and choose the type of chart you want to create (e.g., Column, Line, Pie).
    • Customize the chart as needed.
  3. Add Slicers:
    • Select the PivotTable or chart.
    • Go to the 'Analyze' tab (under PivotTable Tools) and click on 'Insert Slicer'.
    • Choose the fields you want to use as filters.
    • Arrange the slicers on your dashboard.
  4. Connect Slicers to Multiple PivotTables/Charts:
    • Select a slicer.
    • Go to the 'Options' tab (under Slicer Tools) and click on 'Report Connections' or 'PivotTable Connections'.
    • Check the PivotTables and charts you want to connect to the slicer.
  5. Arrange Your Dashboard:
    • Arrange the PivotTables, charts, and slicers on a single sheet to create your dashboard.
    • Adjust the sizes and positions to make the dashboard visually appealing.
Would you like me to save the cleaned data to a new Excel file to help you get started?

prepare that dashboard in power bi

To prepare a dashboard in Power BI, follow these steps:
  1. Open Power BI Desktop:
    • Launch Power BI Desktop on your computer.
  2. Load the Data:
    • Click on 'Get Data' in the Home tab.
    • Select 'Excel' and navigate to the cleaned Excel file you downloaded (Cleaned_Adarsh_Sir_July_24.xlsx).
    • Select the sheets you want to load and click 'Load'.
  3. Create Relationships (if needed):
    • If your data spans multiple tables, you may need to create relationships between them.
    • Go to the 'Model' view and drag fields to create relationships.
  4. Create Visualizations:
    • Go to the 'Report' view.
    • Use the fields pane to drag and drop fields onto the canvas to create visualizations (e.g., bar charts, line charts, pie charts).
    • Customize the visualizations using the formatting options.
  5. Add Slicers for Filtering:
    • Click on the slicer icon in the Visualizations pane.
    • Drag a field to the slicer to create a filter.
    • Arrange the slicers on your dashboard.
  6. Arrange Your Dashboard:
    • Arrange the visualizations and slicers on the canvas to create your dashboard.
    • Adjust the sizes and positions to make the dashboard visually appealing.
  7. Publish the Dashboard:
    • Once you are satisfied with your dashboard, you can publish it to the Power BI service.
    • Click on 'Publish' in the Home tab and follow the prompts to publish your dashboard.
Would you like any specific visualizations or insights to be highlighted in the Power BI dashboard?
Get started with Vizly