Quick answer: Two tools turn raw Excel data into clear pictures: charts (built-in: column, line, pie, combo) and Power Query (Get & Transform — imports, cleans and reshapes data without formulas). Charts answer “how does this look?”; Power Query answers “how do I get this data ready?” — and both are refreshable, not one-off. Start from our essential formulas guide if lookups still trip you up.
Choosing the right chart in five seconds
| Question you’re asking | Chart |
|---|---|
| Comparison across categories | Column or bar |
| Trend over time | Line (never a pie) |
| Part of a whole, few categories | Pie or doughnut (≤5 slices) |
| Relationship between two numbers | Scatter |
| Distribution of values | Histogram |
| Value + its trend together | Combo (column + line, secondary axis) |
Steps: select your data (labels included) → Insert → pick the chart → fix the three things Excel gets wrong: give the chart a title that states the finding (“Chennai doubled revenue in Q3”, not “Revenue”), delete gridlines and legends you don’t need, and sort data bars descending so the story reads top-down.
Power Query: the cleaning tool hiding in the Data tab
Power Query (Data → Get Data / From Text-CSV) records every cleaning step you take — removing columns, splitting names, changing types, filtering rows — and replays them in order on every refresh. That means the monthly CSV export that takes 30 minutes of copy-paste-delete becomes one click:
- Import: Data → Get Data → From Text/CSV → pick the export file
- Clean: in the editor — remove columns, Replace Values, Split Column, Change Type (fix those text-numbers)
- Load: Close & Load To… → Table on a new sheet
- Refresh monthly: replace the source file → Data → Refresh All
The killer feature: steps are recorded, not scripted. No formulas to break, no macros to maintain — and one query can merge two files on a shared column (Merge Queries) exactly like a database join. Combine it with the pivot workflow in our PivotTable guide and the raw-export-to-summary pipeline becomes almost entirely click-driven.
For dashboards built on real company data with an instructor reviewing every step, Ampersand Academy teaches Advanced Excel one-to-one.
Frequently asked questions
What is Power Query in Excel?
Power Query is Excel’s built-in data import and cleaning tool (Data tab, Get and Transform). It records your cleaning steps and replays them on any refresh, turning repetitive cleanup into a one-click refresh.
When should I use a line chart instead of a pie chart?
Use a line chart whenever the x-axis is time or an ordered sequence – trends need lines. Pies only make sense for a few categories adding to one hundred percent at a single point in time.
Do Power Query steps break when the source file changes?
No – that is the point. If column names stay the same, refreshing replays every step on the new data. Renamed or removed columns are the only changes that need the query opened and fixed.
Can Power Query combine multiple files into one table?
Yes. Combine Files from a folder merges every file with the same structure into one table, and Merge Queries joins two tables on a shared column like a database join.
Which chart shows percentages of a total best?
For few categories, a pie or doughnut; for many, a horizontal bar sorted descending with data labels, which stays readable where a ten-slice pie does not.