8.2 Column and Bar Charts
Column (vertical) – compare categories or show a few time periods. Bar (horizontal) – long category names or rankings.
| Variant | Use | Example |
|---|---|---|
| Clustered | Compare series side by side | City sales per month |
| Stacked | Parts of a total | FreshGo + MarketRush per city |
| 100% Stacked | Share within each category | Platform mix per city |
| Bar (sorted) | Ranking | Top 10 products |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Neha Shah) are made up for this lesson on column and bar charts.
Steps in Excel – ranked bar chart
- Sort the product summary ascending by sales (Excel draws the first row at the bottom of a bar chart).
- Insert › Charts › Insert Column or Bar Chart › Clustered Bar.
- Ctrl + 1 on the bars › Series Options › Gap Width 60%.
- + › Data Labels › Outside End; delete the horizontal axis and gridlines.
Ravindra Bagale's Tip
In a bar chart, the biggest item should be at the top, but many students sort descending and it goes to the bottom. For a bar chart, sort the data ascending, or tick Categories in reverse order on the vertical axis. Always start a column chart's value axis at 0.
Ravindra Bagale's Tip – मराठी
Bar chart मध्ये सगळ्यात मोठा item वरती यायला हवा, पण बरेच students descending sort करतात आणि तो खाली जातो. Bar chart साठी data ascending sort करा, किंवा vertical axis वर Categories in reverse order tick करा. Column chart ची value axis नेहमी 0 पासून सुरू ठेवा.
Ravindra Bagale's Tip – हिंदी
Bar chart में सबसे बड़ा item ऊपर आना चाहिए, पर बहुत से students descending sort करते हैं और वह नीचे चला जाता है. Bar chart के लिए data ascending sort करो, या vertical axis पर Categories in reverse order tick करो. Column chart की value axis हमेशा 0 से शुरू रखो.
Worked example — column and bar charts
Why
Vikram Rao in Pune and Emma Clarke abroad both need a clear, repeatable way to use column and bar charts on a Pearl Textiles sheet without breaking totals.
What
Column And Bar Charts is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for column and bar charts.
- Enter a small made-up value for Vikram Rao using £ where money appears.
- Add a second value for Emma Clarke using $ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once column and bar charts is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of column and bar charts on a blank sheet for CityBasket Retail.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Aarti Kulkarni and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for James O'Neill.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply column and bar charts to a 5-row made-up CityBasket Retail table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with column and bar charts.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as CityBasketR_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Total quantity (Easy): Add up the "Quantity" column.
- Average unit (Easy): Average of the Unit price ($) column, rounded to 2 decimals.
- Rows in Website (Easy): How many rows have Category/group "Website"? (the "Channel" column)
- Largest quantity (Easy): The largest value in the "Quantity" column.
- Quantity for Website (Medium): Total of the Quantity column where Channel is "Website".
- Website in Austin (Medium): Total of the Quantity column where Channel is "Website" and City is "Austin".
- Best in Website (Medium): Largest value in the "Unit price ($)" column where "Channel" is "Website".
- Value of row 2 (Medium): Multiply the first-row Quantity by the first-row Unit price ($).
- Look up Noah Wilson (Medium): The Quantity for "Noah Wilson" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Quantity" times "Unit price ($)" for all rows.
- Share of Website (Hard): Share of the "Quantity" column that is "Website", rounded to 3 decimals.
- Total "Quantity" for Marketplace (Hard): Add up the "Quantity" column for the rows where "Channel" is "Marketplace".
- Above average? (Hard): "Above" if the "Unit price ($)" value in the first row is above the average of the "Unit price ($)" column, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
