To build simple and advanced visualizations in Tableau using Microsoft Excel as your data
source, follow the steps below. I will guide you through connecting to an Excel file, building
visualizations, and creating a dashboard.
Step 1: Prepare Your Data in Excel
Ensure you have an Excel file with data ready for analysis. Here's an example dataset structure
you might use:
[Link]:
Date Sales Profit Category Region
2024-01-01 500 120 Furniture East
2024-01-02 300 50 Electronics West
2024-01-03 450 100 Furniture North
2024-01-04 600 200 Clothing South
2024-01-05 700 250 Clothing East
... ... ... ... ...
Step 2: Connect Tableau to the Excel Data
1. Open Tableau: Launch Tableau Desktop.
2. Connect to Excel:
o Under Connect, choose Microsoft Excel.
o Navigate to your Excel file (e.g., [Link]) and select it.
o Tableau will load the spreadsheet and show the available sheets.
3. Select Your Data Sheet:
o Drag the relevant sheet (e.g., Sheet1) into the canvas area to load the data into
Tableau.
o Tableau will display a preview of your data.
4. Go to Worksheet: Click the Sheet1 tab to start building your visualization.
Step 3: Build Simple Visualization in Tableau
For a simple visualization, let's create a Bar Chart showing Total Sales by Region.
1. Drag Fields to Shelves:
o Drag Region to the Rows shelf.
o Drag Sales to the Columns shelf.
2. Add Color:
o Drag Category to the Color mark on the Marks card to color-code each bar by
product category.
3. Format the Visualization:
o Right-click on Sales in the Columns shelf and choose Quick Table Calculation
> Total Sales.
o You can further adjust labels, colors, and axis settings.
Result: A simple bar chart showing the total sales for each region, color-coded by product
category.
Step 4: Build Advanced Visualization in Tableau
For an advanced visualization, let's create a Dual-Axis Line Chart comparing Sales and Profit
over time.
Step 4.1: Create a Sales Line Chart
1. Drag Fields to Shelves:
o Drag Date to the Columns shelf.
o Drag Sales to the Rows shelf.
2. Change the Mark Type:
o In the Marks card, change the mark type to Line.
Step 4.2: Add Profit as a Dual Axis
1. Add Profit:
o Drag Profit to the Rows shelf (next to Sales). Tableau will add a new chart for
Profit.
2. Create Dual-Axis:
o Right-click on the Profit axis (the second axis on the right) and select Dual
Axis. This will overlay the Profit line chart on top of the Sales line chart.
3. Synchronize Axis:
o Right-click on the second axis (Profit) and choose Synchronize Axis so both the
Sales and Profit lines use the same scale.
4. Customize Marks:
o On the Marks card, select Sales and change the mark type to a bar.
o Select Profit and keep it as a line. This creates a combined bar and line chart.
Step 4.3: Add Category Breakdown
1. Add Category to Color:
o Drag Category to the Color shelf in the Marks card for both Sales and Profit.
This will color-code each category in the chart.
2. Add Filters:
o Drag Region to the Filters shelf to create a region-based filter. You can also
right-click on Region and select Show Filter so users can interactively filter the
chart by region.
Result: A dual-axis chart where Sales is displayed as bars and Profit as a line, both broken down
by product category and with region filters.
Step 5: Create a Dashboard
Let’s combine multiple visualizations into a single interactive dashboard.
Step 5.1: Create Multiple Sheets
1. Sheet 1:
o Create a bar chart showing Sales by Region (like the simple visualization above).
2. Sheet 2:
o Create a line chart showing Sales and Profit over Time (like the advanced
visualization above).
3. Sheet 3:
o Create a pie chart showing Sales by Category:
Drag Category to the Rows shelf.
Drag Sales to the Columns shelf.
Change the mark type to Pie.
Step 5.2: Create the Dashboard
1. New Dashboard:
o Click the New Dashboard tab at the bottom of the Tableau window.
2. Drag Sheets to Dashboard:
o Drag Sheet 1 (Bar Chart), Sheet 2 (Dual-Axis Chart), and Sheet 3 (Pie Chart)
into the dashboard canvas.
3. Adjust Layout:
o Rearrange the sheets to create an organized layout. You can resize them and
adjust spacing.
4. Add Interactivity:
o Go to Dashboard > Actions, and add Filter Actions to link the visualizations.
o For example, when users click on a region in the bar chart (Sheet 1), it should
filter the sales and profit line chart (Sheet 2) and the pie chart (Sheet 3) to show
only data for that region.
5. Add Titles and Labels:
o Add descriptive titles to each chart and the dashboard itself by dragging the Text
object from the Objects pane.
Result: A comprehensive dashboard that includes:
A bar chart showing sales by region.
A dual-axis chart comparing sales and profit over time.
A pie chart showing sales by product category.
Interactive filters linking all the visualizations.
Step 6: Save and Share
1. Save Workbook:
o Save the Tableau workbook with data embedded as a .twbx file so you can easily
share it with others.
2. Publish:
o Publish the workbook to Tableau Server, Tableau Online, or export it as a PDF or
image for static distribution.
Example Simple Visualization: Bar Chart of Sales by Region
plaintext
Copy code
East | ████████
West | ████
North | █████
South | ██████
Example Advanced Visualization: Dual-Axis Chart
plaintext
Copy code
Date | ███████ Bars (Sales)
| | Line (Profit)
2024-01 | ____/
2024-02 | ___/
2024-03 |___/
By following these steps, you can easily create both simple and advanced visualizations using
Tableau with data sourced from Excel.
4o
ChatGPT can make