At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
This is the final result we will create, a sales report for a grocery store that automatically calculates the total price of each item, total sales, average price, highest and lowest prices, and total including tax. If there are any numbers that change, the results will automatically update. Hello, welcome to the Excel tutorial for beginners, basic formulas in Excel. We will learn how to calculate total prices, sum columns, find averages, maximum values, minimum values, and add tax, all automatically using formulas.
Step 1: Store sales data
This is the sales data of a grocery store, including item names, quantities, unit prices, and an empty total price column. We will not calculate using a calculator. We will let Excel do the calculations using formulas. The first rule of Excel formulas is that they always start with an equals sign.
Step 2: First formula, multiply quantity by price
Click cell D2. Type the equals sign, then B2, asterisk, and C2, then press Enter. The asterisk means multiplication. B2 is the quantity, and C2 is the unit price, so the result is the total price of rice. Click cell D2 again, and look at the formula bar, the formula is still saved, while the result is displayed in the cell.
Step 3: Copy the formula using the fill handle
We don't need to type the formula repeatedly. With cell D2 selected, double-click the small dot in the bottom-right corner of the cell. That dot is called the fill handle. Excel automatically copies the formula to all cells below it, and the cell references adjust automatically. See, the formula in D3 becomes B3xC3.
Step 4: Summing with SUM
Now we will calculate the total sales. In cell A12, type Total Sales. Then in cell D12, type equals SUM, open parenthesis, D2:D11, close parenthesis, and press Enter. SUM adds all the numbers in the range D2 to D11. The colon means up to.
Step 5: AutoSum with Alt =
There is a faster way, called AutoSum. Click cell B12, then press Alt and the equals sign at the same time. Excel guesses the range to be summed and writes the SUM formula. Press Enter, and we get the total number of items sold.
Step 6: AVERAGE, MAX, MIN, and COUNTA
Besides SUM, there are several functions that are very often used. We add it below the table. AVERAGE for the average unit price. MAX for the highest price. MIN for the lowest price. And COUNTA to count the number of rows containing item names. Type the labels in column A, then the formulas in column D. Now let's add 11% tax.
Step 7: Tax with absolute cell reference
In F1 type PPN, and in G1 type 11%. In E1 type the title for total plus PPN. In E2 type equals D2 times. Open parenthesis, 1 plus G1. Before closing the parenthesis, press F4. The dollar sign appears around G1. That means the cell address is absolute, so it won't shift when the formula is copied. Close the parenthesis, press Enter, then double-click the fill handle to copy down.
Step 8: Formatting numbers
Let's tidy up the appearance. Select C2 to E12 and also D14 to D16, then press Ctrl 1, Tab Number, Custom, and format in rupiah the same as in the previous tutorial. Bold the total row with Ctrl B. Finally, widen the columns so all numbers are clearly visible.
Step 9: Results change automatically
This is the power of formulas. Try changing the amount of rice from 12 to 20. See, the total price, total sales, and total plus tax change automatically. Nothing needs to be recalculated. Press Ctrl S to save.
Done! Basic Excel formulas are now mastered
Done. We have learned to write formulas with the equal sign. Copy formulas using the fill handle, using SEM, OtoSum, Eprej, Max, Min, and Count A, as well as absolute cell references. Thank you for watching. See you in other tutorial series.
Cerdas Inovasi Digital
Want to master Excel for work and business? Cerdas Inovasi Digital offers Excel and Office courses, template creation services, reports, and Excel dashboards, as well as sells the practice files of this tutorial. Contact us via WhatsApp, or visit cerdasinovasidigital.com Thank you for watching. See you in other tutorial series. Thank you for watching.
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
52 video classes1,276 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
