Excel and Google Sheets basics: a practical guide for work

Working with spreadsheets: entering data, formatting, SUM, AVERAGE, COUNT and IF, sorting, filtering, charts and a step-by-step exercise.

The spreadsheet is one of the most widely used office tools. Accountants, storekeepers, teachers, HR staff and small shop owners all work with tables every day. “Ability to work in Excel” appears in almost every job advert. The good news is that a handful of basic skills covers most everyday work. In this guide we go through the basics shared by Microsoft Excel and the free Google Sheets: the interface, entering data properly, formatting, core formulas, sorting and filtering, charts, common mistakes, a step-by-step practice exercise and useful keyboard shortcuts.

Getting to know the interface

Excel and Google Sheets look slightly different, but the core ideas are the same.

Workbook (file): the whole document. It can contain several sheets.
Sheet: the tabs at the bottom. For example, one sheet per month.
Columns are labelled with letters (A, B, C…), rows with numbers (1, 2, 3…).
Cell: where a column and a row meet. Its address, for example B3, means column B, row 3.
Formula bar: the long field above the grid. It shows the real content of the selected cell: text, a number or a formula.
Range: a group of cells, for example B2:B10, meaning column B from row 2 to row 10.

One important difference: Google Sheets runs in the browser and saves changes automatically, while in Excel you need to remember to save the file regularly. Another point: depending on regional settings, the separator between formula arguments is either a comma or a semicolon. If a formula returns an error, try switching the separator. In some language versions the function names are translated too; this guide uses the English names.

Entering data properly and formatting

The cleaner the table, the easier everything that follows. Many problems start not with formulas but with badly entered data.

Rules for entering data

The first row holds headers. Give every column a short, clear name: “Item”, “Received”, “Date”.
One value per cell. Type “12”, not “12 kg”, and put the unit in the header: “Quantity (kg)”. Otherwise the program treats the number as text and cannot calculate with it.
One type of data per column. Only dates in the date column, only numbers in a number column.
Do not leave blank rows or columns. A blank row “breaks” sorting and filtering.
Enter dates in one format. For example day.month.year. When the program recognises a date, it aligns it to the right of the cell.
Be careful with merged cells. They look neat but break sorting and copying.

A quick test: numbers are usually right-aligned and text left-aligned. If your numbers sit on the left, they may be stored as text.

Formatting

Formatting makes data easier to read but does not change its content.

Header row: bold font and a light fill colour.
Column width: double-click the column border and the width fits the content.
Number format: set the number of decimal places for fractions, and use the percentage format for shares.
Date format: one date style per table.
Borders: thin lines around and inside the table.
Freeze the header: in the “View” menu, freeze the first row so the headers stay visible as you scroll down.
Conditional formatting: cells that meet a condition are coloured automatically. For example, a red fill when the stock is below 10.

Use colour sparingly: 2–3 colours are enough. A very colourful table is harder to read.

Core formulas: SUM, AVERAGE, COUNT, IF

Every formula starts with an equals sign (=). Formulas use cell addresses rather than fixed numbers, so when the data changes the result updates automatically.

=SUM(B2:B10): the total of the numbers in the range. For example, the total quantity of goods received during the month.
=AVERAGE(B2:B10): the average value. For example, the average quantity sold per day.
=COUNT(B2:B10): the number of cells containing numbers. To count non-empty cells with text, use COUNTA.
=IF(B2<10,“Low”,“Enough”): a condition. If the value in B2 is less than 10, the cell shows “Low”; otherwise “Enough”.
=MAX(B2:B10) and =MIN(B2:B10): the largest and smallest value.

You can write a formula in one cell and then “fill” it down: drag the small square in the bottom-right corner of the cell downwards. The program adjusts the addresses for each row automatically: B2 becomes B3, B4 and so on.

Sometimes an address must not change, for example when every row refers to a single “minimum stock” cell. Select the address in the formula and press F4: the address is “locked” and stays the same when you fill. This is called an absolute reference.

Sorting and filtering

As data grows, finding the row you need gets harder. Sorting and filters solve that.

Sorting puts rows in order: alphabetically, numbers from largest to smallest, dates from oldest to newest.

Select any cell inside the table.
Choose “Sort” from the “Data” menu.
Pick the column and the order.

Watch out: if you select and sort a single column, it gets “detached” from the others and the data is scrambled. Always make sure the whole table is being sorted.

A filter temporarily hides rows that do not meet a condition, without deleting them. Select the header row and turn on the filter; a small arrow appears next to each header. Use it to show, for example, only one category of products, or only those with stock below 10. Turn the filter off and all rows come back.

Simple charts

A chart makes numbers clear at a glance. Three types are enough to start:

Column chart: to compare categories (for example, how much of each product was sold).
Line chart: for change over time (for example, goods received by week).
Pie chart: for parts of a whole, with no more than 5–6 slices.

To create a chart:

Select the columns you need, including their headers.
Choose “Chart” from the “Insert” menu.
Pick the type, write a clear title and label the axes.

The rule of a good chart: one chart, one message. If you cannot state its conclusion in one sentence, simplify it.

Practice exercise: a small shop stock table

Now let us put it all together. Imagine a shop owner called Gulnora who tracks the stock of her family grocery shop in a paper notebook and often loses count. We will build her a monthly stock table. The example is illustrative and the quantities are made up.

Step 1. Headers. In A1 to G1 type: Item, Unit, Opening stock, Received, Sold, Current stock, Status.

Step 2. Data. In rows 2–7 enter six products, for example:

Flour — kg — 50 — 100 — 120
Rice — kg — 40 — 60 — 85
Sugar — kg — 30 — 50 — 72
Vegetable oil — litre — 20 — 24 — 38
Tea — box — 15 — 20 — 30
Salt — kg — 10 — 10 — 12

Step 3. Current stock formula. In F2 type =C2+D2-E2 and fill the formula down to F7.

Step 4. Status column. In G2 type =IF(F2<10,“Reorder”,“Enough”) and fill it down. Now you can see at a glance which items need restocking.

Step 5. Summary rows. In A9 type “Total sold” and in E9 =SUM(E2:E7). In A10 “Average sold” and in E10 =AVERAGE(E2:E7). In A11 “Number of items” and in E11 =COUNT(E2:E7).

Step 6. Formatting. Make the headers bold, fit the column widths and freeze the first row. Use conditional formatting to fill the “Reorder” cells in light red.

Step 7. Sorting and filtering. Sort the table by “Sold” from largest to smallest so the best-selling item rises to the top. Then use a filter to show only items with the “Reorder” status.

Step 8. Chart. Select columns A and E (Item and Sold) and create a column chart. Title: “Sold this month”.

Step 9. Check. Change the received quantity for one product. The current stock, status, summary rows and chart should all update automatically. If they do not, review your formulas.

You can use the same structure at home too: for example, a monthly food supply list or a list of your children’s school supplies.

Keyboard shortcuts

Shortcuts speed up your work noticeably. These are for Windows; on macOS, Cmd usually replaces Ctrl.

Ctrl + C / Ctrl + V: copy and paste.
Ctrl + Z / Ctrl + Y: undo and redo.
Ctrl + S: save (in Excel).
Ctrl + arrow: jump to the edge of a block of data.
Ctrl + Shift + arrow: select to the edge of the block.
Ctrl + Home: go back to cell A1.
Ctrl + F: find.
F2: edit the current cell.
F4: lock an address in a formula (in Excel).
Alt + =: AutoSum below the selected column (in Excel).
Ctrl + Shift + L: turn the filter on and off (in Excel).

Make 2–3 new shortcuts a habit each week, and within a month you will reach for the mouse much less.

Common mistakes

Entering numbers as text. “12 kg” or a space before a number breaks calculations.
Typing results by hand. If you work out a total on a calculator and type it in, it goes out of date as soon as the data changes. Always use formulas.
Sorting a single column. Rows get scrambled; select the whole table.
Overwriting a formula. If a number is accidentally typed into a formula cell, the error is invisible. Protect important columns or mark them with a different colour.
Everything on one sheet. Keep data, calculations and the report in separate areas or sheets.
No backup. Keep a copy of important files or use cloud storage.
Ignoring error codes. #DIV/0!, #VALUE!, #REF! are the program telling you something is wrong. Check the formula.

Conclusion

Working with spreadsheets is not difficult: cleanly entered data, a few core formulas, sorting, filtering and a simple chart cover most everyday tasks. The best way to learn is with a real task: try turning your household supplies, a small shop’s stock or a list from your workplace into a table.

If you would like to learn computer literacy and office software systematically with a teacher, take a look at our association’s free training programmes. When you are ready, submit an application and our specialists will get in touch.