Data Analytics Guide: Spreadsheets, SQL and Dashboards

The pivot table is where learners first start thinking like analysts: rows become groups, columns become comparisons, and values become the measure being summarised. A few clicks turn thousands of sales rows into a small table of monthly totals by category. Because this group-then-summarise logic is exactly what GROUP BY does in SQL, time spent on pivot tables pays off in the next phase.

Three families of formulas sit alongside the pivot. SUMIFS and COUNTIFS add or count with several conditions at once, such as drinks sales in June. VLOOKUP, XLOOKUP or INDEX MATCH bring in details from another table, which is the seed of a JOIN. IF and IFS create new category columns, for example flagging orders above a certain value as large orders.

The most common snag is a lookup returning #N/A because the key is formatted differently in the two tables, say a product code stored as a number in one and as text in the other. Learners who check data types before matching rarely trip here. For practice, write three questions about one dataset, answer them with a pivot table, then answer them again with SUMIFS. When both numbers agree, the logic has stuck.

Once learners start comparing two variables, correlation appears, and with it the oldest trap in data analysis. Two figures that rise together, ice cream sales and swimming pool visits for instance, may both be driven by a third factor such as the season. The CORREL function shows how strongly two numbers move together. Claiming cause and effect needs a different design, such as an experiment or a comparison between genuinely similar groups.

Outliers deserve the same care. A transaction worth a hundred times the average might be a typo, or it might be a real bulk order that turns out to be the most interesting finding in the dataset. The healthy habit is to trace where an outlier came from, note it down, and only then decide whether to separate it, correct it or keep it.

The choice of metric steers the whole analysis. Views, followers and downloads feel impressive, yet they may have little to do with business goals such as repeat purchases or revenue per customer. Learners practise writing an explicit definition for every metric: what the numerator is, what the denominator is, and which time period it covers. A written definition means two people calculating the same metric arrive at the same number.

Company data rarely lives in a single table. A shop database usually separates customers, orders, order lines and products, linking them through keys such as customer_id or product_id. JOIN brings those tables together, and it is among the topics that come up most often in SQL interviews. With an INNER JOIN, only rows that have a partner in both tables survive, while a LEFT JOIN holds on to every row of the left table and puts NULL in the columns whose partner is missing.

The biggest trap is row multiplication. One order can have five rows in the order lines table. If a shipping fee recorded once per order is summed after joining to the order lines, it gets counted five times, and the total inflates without any error message. Before writing a JOIN, learners get used to asking whether the relationship between the two tables is one to one, one to many or many to many.

Two simple habits catch this early. First, count the rows before and after every JOIN; if the number climbs for no clear reason, there is multiplication to investigate. Second, summarise the table with many rows using GROUP BY first, and join afterwards. A LEFT JOIN combined with WHERE right_column IS NULL is also handy for finding orphaned records, such as customers who registered and never placed an order.

A correct number still has to be communicated clearly to be useful. Chart choice follows the question being asked: bar charts compare categories, line charts show trends over time, scatter plots show how two variables relate, and maps suit regional data. A pie chart only reads well with a handful of slices; with many slices, a sorted bar chart is almost always clearer. Bar chart axes start at zero so the difference in bar heights stays honest.

Dashboard layout follows the way the eye reads a screen. Key figures sit in the top row as cards, trend charts go beneath them, and category breakdowns come last. Each chart title states its finding, for example eastern region sales fell for three months running, so readers know what to look for. Filters or slicers are added for the dimensions readers actually change, such as period and region. The palette stays small, and a bright colour is reserved for highlighting one thing.

The three tools each have their own character. Power BI is common in organisations already on Microsoft 365, Tableau Public makes it easy to publish dashboards openly for a portfolio, and Looker Studio connects straight to Google Sheets. The visualisation principles are identical across all three, so learners only need to go deep on one first. A simple test: ask someone who has never seen the data to read the dashboard, then ask what they took away in ten seconds.

Every earlier skill comes together in a project. A strong project starts from one specific, measurable question, such as which region's sales have dropped over the last three months, or at what hour city transport is busiest. Two or three supporting questions branch off the main one. Test the question with a single check: if it were answered, which decision could change? A question that changes no decision is usually too broad.

Dataset choice decides half of a project's success. Sources such as Satu Data Indonesia, Statistics Indonesia (BPS), city open data portals and Kaggle offer plenty of variety. Learners read the column dictionary before committing to a dataset, check the licence so it can be republished, and steer clear of datasets that thousands of people have already analysed the same way, the Titanic passenger list being the classic example. Local data that few people have touched makes the analysis feel relevant to recruiters in Indonesia.

Documentation turns a folder of files into evidence of how someone thinks. Each project's README follows the same template: background and question, data source, cleaning steps, method, key findings in business language, recommendation and data limitations. Dashboard screenshots and the Tableau Public link go there too. Three or four projects that each show a different strength, one heavy on SQL, one on data cleaning, one on visualisation, make a complete beginner portfolio.