Lesson 10 · Excel: Zero to Expert
Excel Templates, Data Validation, Printing & Protection (Full Course) | Excel 110
Sheet: Formats Workbook: excel110templates.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by…
- CourseExcel: Zero to Expert
- Lesson10 of 10
- Video1 h 3 min
- FormatExcel workbook · step-by-step guide
What you'll learn
Files for this lesson
Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.
- excel110_templates.xlsx30.0 KB
📝 Step-by-step guide
Excel 110: Templates, Forms, Printing and Protection#
Sheet: Formats
Workbook: excel110_templates.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step with the video. Each section below lists exactly what to click and type.
What makes a good template#
Custom number formats#
In A1 type
Custom number formatsStarting in A3, type these rows (one value per cell, moving right):
|
Value|Shown as|Format code|Used for| |---|---|---|---|Format A1: bold, font size 14
Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1
Starting in A4, type these rows (one value per cell, moving right):
|
72.456|=A4|'0.0"kg"|Weight with a unit| |---|---|---|---|Click B4
Press Ctrl+1
Press Esc
- Format B4: number format 0.0"kg"
B4 now shows seventy two point five kg. One decimal place, rounded for display, with kg on the end. But look at the formula bar. B4 still contains equals A4, and the value underneath is still seventy two point four five six. Only the look has changed. In a format code, a zero means show a digit here, even if it's a zero. Text in double quotes is shown exactly as written.
Starting in A5, type these rows (one value per cell, moving right):
|
1234567|=A5|'#,##0|Thousands separators| |---|---|---|---|Format B5: number format #,##0
B5 shows one comma two three four comma five six seven. Much easier to read. The hash is a digit placeholder that shows a digit only if it's needed, so you don't get leading zeros. The comma switches on the thousands separator. And the zero at the end makes sure a zero value still shows as zero, not an empty cell.
Starting in A6, type these rows (one value per cell, moving right):
|
-1520.5|=A6|'"$"#,##0.00;[Red]-"$"#,##0.00|Money, negatives in red| |---|---|---|---|Format B6: number format "$"#,##0.00;[Red]-"$"#,##0.00
B6 shows minus one thousand five hundred and twenty dollars fifty, in red. The semicolon is the important part. A format code can have up to four sections separated by semicolons: the first for positive numbers, the second for negative numbers, the third for zero, and the fourth for text. Here we gave two sections, so positives use the first pattern and negatives use the second, which is red with a minus sign. The colour name in square brackets works for Red, Blue, Green, Black, White, Yellow, Magenta and Cyan.
Starting in A7, type these rows (one value per cell, moving right):
|
3-Nov-2026|=A7|'dd-mmm-yyyy|Dates| |---|---|---|---|Format B7: number format dd-mmm-yyyy
A7 shows three dash Nov dash twenty six, Excel's own choice. B7 shows zero three dash Nov dash twenty twenty six. In date codes, d is the day, two d's add a leading zero, three d's give the short day name like Tue, and four give the full name. For months, m m m gives Nov, and m m m m gives November. y y y y is the four digit year. Underneath, the date is just a number: forty six thousand three hundred and twenty nine, the count of days since the start of nineteen hundred. The format makes it readable.
Starting in A8, type these rows (one value per cell, moving right):
|
800|=A8|'0000|Postcodes| |---|---|---|---|Format B8: number format 0000
B8 shows zero eight zero zero. That's the postcode for Darwin, which Excel would normally show as just eight hundred.
Starting in A9, type these rows (one value per cell, moving right):
|
42|=A9|'00000|Staff IDs| |---|---|---|---|Format B9: number format 00000
B9 shows zero zero zero four two. Each zero forces a digit, so short numbers get padded with zeros on the left. A tip: this is only for numbers you'll never do maths with. If a code is really an identifier, like a phone number, some people prefer to store it as text instead. Either way works, as long as you're consistent.
Starting in A10, type these rows (one value per cell, moving right):
|
1045|=A10|'"INV-"0000|Invoice numbers| |---|---|---|---|Format B10: number format "INV-"0000
B10 shows INV dash one zero four five. And the cell is still a number, so the next invoice can simply be this one plus one. If you typed the letters into the cell, you'd have text, and you couldn't add one to it.
Starting in A11, type these rows (one value per cell, moving right):
|
1234567|=A11|'#,##0,"K"|Thousands as K| |---|---|---|---|Format B11: number format #,##0,"K"
B11 shows one comma two three five K. A comma at the very end of the number part divides the display by one thousand, and it rounds, so one point two three four million becomes one thousand two hundred and thirty five K. Two commas would divide by a million. Great for dashboards.
Starting in A12, type these rows (one value per cell, moving right):
|
0|=A12|'#,##0;-#,##0;"nil"|Zero as a word| |---|---|---|---|Format B12: number format #,##0;-#,##0;"nil"
Format B3:D12: AutoFit columns
B12 shows the word nil, because the third section is used for zero. Many accountants show zeros as a dash instead, and you can do that with a dash in quotes.
Starting in A14, type these rows (one value per cell, moving right):
|
72.5kg|=A14*2| |---|---|In A15 type the formula
=B4*2
B14 shows hash VALUE. A14 is text, and you can't multiply text. Notice it's also sitting on the left of the cell, Excel's hint that it's text. A15 shows one hundred and forty four point nine one two. B4 looks like it contains kg, but it's really the number seventy two point four five six, so the maths works. So the rule is: never type units, currency symbols or labels into number cells. Put them in the format.
Cell styles and themes#
Sheet: Styles
In A1 type
Quarterly sales by regionStarting in A3, type these rows (one value per cell, moving right):
|
Region|Q1|Q2|Total| |---|---|---|---| |North|18400|21250|=B4+C4| |South|15900|17300|=B5+C5| |East|22100|19800|=B6+C6| |West|12600|14950|=B7+C7|
Starting in A8, type these rows (one value per cell, moving right):
|
Total|=SUM(B4:B7)| |---|---|Fill B8:D8 right (Ctrl+R)
The totals are sixty nine thousand for Q1, seventy three thousand three hundred for Q2, and one hundred and forty two thousand three hundred overall.
- Select A1
- On the ribbon choose Home > Cell Styles > Title
- Select A3:D3
- On the ribbon choose Home > Cell Styles > Heading 3
- Select B4:C7
- On the ribbon choose Home > Cell Styles > Input
- Select D4:D7
- On the ribbon choose Home > Cell Styles > Calculation
- Select A8:D8
- On the ribbon choose Home > Cell Styles > Total
- Format B4:D8: number format $#,##0
- Format B3:D8: AutoFit columns
Look how quickly that came together. The inputs have a soft orange fill with blue text and a thin border. The calculations have a grey fill with bold orange text. The total row has a line above and a double line below. Anyone opening this sheet can tell, without asking, which numbers they're allowed to change.
- On the ribbon choose Page Layout > Themes
- Press Esc
Input cells versus formula cells#
Sheet: Quote
In A1 type
Catering quote calculatorIn A3 type
InputsStarting in A4, type these rows (one value per cell, moving right):
|
Guests|84| |---|---| |Price per head|42.5| |Venue hire|1200| |Discount|5%|Format A3: column width 18
In A9 type
ResultsStarting in A10, type these rows (one value per cell, moving right):
|
Food cost|=B4*B5| |---|---| |Subtotal|=B10+B6| |Discount amount|=-ROUND(B11*B7,2)| |Total ex GST|=B11+B12| |GST|=ROUND(B13*10%,2)| |Total inc GST|=B13+B14|
- Format B5:B6: number format currency
- Format B10:B15: number format currency
- Format A3,A9: bold, font size 12
- Format A1: bold, font size 14
- Format A15:B15: bold, double bottom border
Food cost is three thousand five hundred and seventy dollars. Add the venue and the subtotal is four thousand seven hundred and seventy. The five percent discount takes off two hundred and thirty eight dollars fifty. That leaves four thousand five hundred and thirty one fifty before GST, then GST of four hundred and fifty three dollars fifteen, for a total of four thousand nine hundred and eighty four dollars sixty five.
- Format B4:B7: font colour Blue, fill #FFF2CC
- Format B4:B7: all border
- In A17 type
Yellow cells are inputs. Everything else is calculated. - Format A17: italic
Now the sheet explains itself. The yellow boxes are the only places to type. And the formulas stay in black text with no fill. In the project we'll use the built-in Input style instead, which does the same job with one click. Use whichever you like, but be consistent across the whole workbook.
Data validation with input messages and error alerts#
- Validation: between [1, 500], error_message Guests must be a whole number from 1 to 500., input_message Enter the number of guests, from 1 to 500., range B4, whole True
- Validation: between [0, 0.2], decimal True, error_message Discounts above 20% need a manager's approval., input_message Discount from 0% to 20%., range B7
- Click B4
- In B4 type
900 - Dialog ok: button Cancel, wait 2.5
B4 still says eighty four. If I had clicked Retry, Excel would let me fix the number instead. This is far friendlier than a total that quietly comes out wrong, and far friendlier than Excel's generic message, so always write your own error text, and say what is allowed, not just that something is wrong.
Dropdown lists from a table#
Sheet: Lists
In A1 type
Catering packagesStarting in A3, type these rows (one value per cell, moving right):
|
Package|Price| |---|---| |Canapes|38| |Buffet|42.5| |Banquet|55| |Premium|68|Table: name Packages, range A3:B7
Format B4:B7: number format currency
Format A1: bold, font size 14
- Formulas → Name Manager → New: name
PackageList, Refers to=Packages[Package]
- In D4 type
Package - Validation: error_message Please choose a package from the list., input_message Choose a catering package., list =PackageList, range E4
- In E4 type
Buffet - Format E4: font colour Blue, fill #FFF2CC, all border
- Format D3: bold, font size 12
- Format E3: column width 14
- In B5 type the formula
=XLOOKUP(E4,Packages[Package],Packages[Price]) - Format B5: font colour Black, Text 1, fill No Fill, none border
B5 shows forty two dollars fifty, the Buffet price, so all the totals are unchanged.
- In E4 type
Banquet
The price jumps to fifty five dollars. Food cost is now four thousand six hundred and twenty, the subtotal five thousand eight hundred and twenty, the discount two hundred and ninety one dollars, and the total ex GST five thousand five hundred and twenty nine. GST is five hundred and fifty two dollars ninety, and the total including GST is six thousand and eighty one dollars ninety.
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Lists!A8: Garden party|47| |---|---|Format B8: number format currency
Press Alt+Down
Press Esc
Notes and comments#
- Click B6
- On the ribbon choose Review > Notes > New Note
- Type
Venue quote valid until 31 December 2026. Check before sending. - Press Esc
B6 now has a small red triangle in its top right corner. Hover the mouse over the cell and the note appears. Move away and it hides again.
- On the ribbon choose Review > Notes > Show All Notes
- On the ribbon choose Review > Notes > Show All Notes
Hyperlinks between sheets#
Sheet: Contents
In A1 type
Workbook contentsStarting in A3, type these rows (one value per cell, moving right):
|
Sheet|Link|What it contains| |---|---|---| |Formats|=HYPERLINK("#Formats!A1","Go to Formats")|Custom number formats| |Styles|=HYPERLINK("#Styles!A1","Go to Styles")|Cell styles and themes| |Quote|=HYPERLINK("#Quote!A1","Go to Quote")|Catering quote calculator| |Lists|=HYPERLINK("#Lists!A1","Go to Lists")|Packages for the dropdown|
- Format A1: bold, font size 14
- Format A3:C3: bold, font colour White, Background 1, fill Blue, Accent 1
- Format C3:C8: AutoFit columns
- Format B3: column width 16
The links appear in blue, underlined text, just like on a web page. If a sheet name has spaces, wrap it in single quotes inside the address, like hash, quote, Sales Report, quote, exclamation mark, A1.
- Go to the Quote sheet
And we're straight on the Quote sheet, with A1 selected. One click, no hunting through tabs.
- Go to the Contents sheet
And we're back on the Contents sheet. In a big workbook, put a Back to contents link in the same corner of every sheet, and people will never get lost.
Page setup and printing#
Sheet: Report
- In A1 type
October 2026 sales by order - Paste: at A3, rows [['Date', 'Region', 'Rep', 'Product', 'Units', 'Revenue'], ['1-Oct-2026', 'West', 'Noah', 'Cleaning tablets', 17, 314.5], ['1-Oct-2026', 'West', 'Noah', 'Milk jug', 19, 474.05], ['1-Oct-2026', 'North', 'Aisha', 'Cleaning tablets', 28, 518.0], ['2-Oct-2026', 'East', 'Grace', 'Espresso beans', 9, 292.5], ['2-Oct-2026', 'North', 'Aisha', 'Decaf beans', 30, 1020.0], ['3-Oct-2026', 'North', 'Aisha', 'Cleaning tablets', 29, 536.5], ['3-Oct-2026', 'South', 'Liam', 'Takeaway cups', 20, 136.0], ['4-Oct-2026', 'East', 'Grace', 'Takeaway cups', 34, 231.2], ['4-Oct-2026', 'North', 'Aisha', 'Milk jug', 37, 923.15], ['4-Oct-2026', 'West', 'Noah', 'Takeaway cups', 10, 68.0], ['5-Oct-2026', 'South', 'Liam', 'Takeaway cups', 28, 190.4], ['5-Oct-2026', 'West', 'Noah', 'Decaf beans', 13, 442.0], ['6-Oct-2026', 'West', 'Noah', 'Decaf beans', 7, 238.0], ['6-Oct-2026', 'West', 'Noah', 'Takeaway cups', 23, 156.4], ['7-Oct-2026', 'South', 'Liam', 'Decaf beans', 21, 714.0], ['7-Oct-2026', 'South', 'Liam', 'Decaf beans', 19, 646.0], ['8-Oct-2026', 'South', 'Liam', 'Espresso beans', 40, 1300.0], ['8-Oct-2026', 'East', 'Grace', 'Decaf beans', 2, 68.0], ['8-Oct-2026', 'West', 'Noah', 'Milk jug', 14, 349.3], ['9-Oct-2026', 'South', 'Liam', 'Milk jug', 34, 848.3], ['9-Oct-2026', 'West', 'Noah', 'Takeaway cups', 34, 231.2], ['10-Oct-2026', 'East', 'Grace', 'Espresso beans', 21, 682.5], ['10-Oct-2026', 'West', 'Noah', 'Milk jug', 35, 873.25], ['11-Oct-2026', 'North', 'Aisha', 'Takeaway cups', 29, 197.2], ['11-Oct-2026', 'North', 'Aisha', 'Espresso beans', 7, 227.5], ['12-Oct-2026', 'West', 'Noah', 'Cleaning tablets', 12, 222.0], ['12-Oct-2026', 'South', 'Liam', 'Cleaning tablets', 9, 166.5], ['12-Oct-2026', 'North', 'Aisha', 'Espresso beans', 10, 325.0], ['13-Oct-2026', 'South', 'Liam', 'Decaf beans', 9, 306.0], ['13-Oct-2026', 'East', 'Grace', 'Espresso beans', 35, 1137.5], ['14-Oct-2026', 'North', 'Aisha', 'Decaf beans', 34, 1156.0], ['14-Oct-2026', 'North', 'Aisha', 'Espresso beans', 4, 130.0], ['15-Oct-2026', 'South', 'Liam', 'Espresso beans', 5, 162.5], ['15-Oct-2026', 'East', 'Grace', 'Decaf beans', 37, 1258.0], ['16-Oct-2026', 'North', 'Aisha', 'Espresso beans', 23, 747.5], ['16-Oct-2026', 'North', 'Aisha', 'Espresso beans', 2, 65.0], ['16-Oct-2026', 'West', 'Noah', 'Espresso beans', 23, 747.5], ['17-Oct-2026', 'South', 'Liam', 'Milk jug', 31, 773.45], ['17-Oct-2026', 'West', 'Noah', 'Cleaning tablets', 19, 351.5], ['18-Oct-2026', 'West', 'Noah', 'Decaf beans', 19, 646.0], ['18-Oct-2026', 'South', 'Liam', 'Cleaning tablets', 16, 296.0], ['19-Oct-2026', 'West', 'Noah', 'Cleaning tablets', 33, 610.5], ['19-Oct-2026', 'East', 'Grace', 'Decaf beans', 17, 578.0], ['20-Oct-2026', 'West', 'Noah', 'Cleaning tablets', 14, 259.0], ['20-Oct-2026', 'West', 'Noah', 'Decaf beans', 9, 306.0], ['20-Oct-2026', 'North', 'Aisha', 'Decaf beans', 10, 340.0], ['21-Oct-2026', 'North', 'Aisha', 'Milk jug', 10, 249.5], ['21-Oct-2026', 'West', 'Noah', 'Espresso beans', 20, 650.0], ['22-Oct-2026', 'East', 'Grace', 'Decaf beans', 12, 408.0], ['22-Oct-2026', 'West', 'Noah', 'Milk jug', 14, 349.3], ['23-Oct-2026', 'South', 'Liam', 'Cleaning tablets', 26, 481.0], ['23-Oct-2026', 'South', 'Liam', 'Espresso beans', 6, 195.0], ['24-Oct-2026', 'South', 'Liam', 'Espresso beans', 10, 325.0], ['24-Oct-2026', 'West', 'Noah', 'Espresso beans', 8, 260.0], ['24-Oct-2026', 'North', 'Aisha', 'Takeaway cups', 16, 108.8], ['25-Oct-2026', 'West', 'Noah', 'Cleaning tablets', 39, 721.5], ['25-Oct-2026', 'South', 'Liam', 'Decaf beans', 29, 986.0], ['26-Oct-2026', 'South', 'Liam', 'Cleaning tablets', 37, 684.5], ['26-Oct-2026', 'South', 'Liam', 'Milk jug', 24, 598.8], ['27-Oct-2026', 'West', 'Noah', 'Decaf beans', 12, 408.0], ['27-Oct-2026', 'South', 'Liam', 'Takeaway cups', 13, 88.4], ['28-Oct-2026', 'East', 'Grace', 'Decaf beans', 39, 1326.0], ['28-Oct-2026', 'West', 'Noah', 'Cleaning tablets', 18, 333.0], ['28-Oct-2026', 'East', 'Grace', 'Espresso beans', 31, 1007.5], ['29-Oct-2026', 'East', 'Grace', 'Cleaning tablets', 35, 647.5], ['29-Oct-2026', 'East', 'Grace', 'Cleaning tablets', 18, 333.0], ['30-Oct-2026', 'East', 'Grace', 'Takeaway cups', 26, 176.8], ['30-Oct-2026', 'North', 'Aisha', 'Milk jug', 12, 299.4], ['31-Oct-2026', 'South', 'Liam', 'Milk jug', 19, 474.05], ['31-Oct-2026', 'East', 'Grace', 'Milk jug', 36, 898.2]]
- Select F74 and click AutoSum (Alt+=)
- Format A1: bold, font size 14
- Format A3:F3: bold, font colour White, Background 1, fill Blue, Accent 1
- Format F4:F74: number format currency
- Format B3:F74: AutoFit columns
The total revenue for October is thirty four thousand two hundred and seventy one dollars fifteen. Seventy rows is too long for one printed page, so this is a perfect practice report.
Orientation, size and margins#
- On the ribbon choose Page Layout > Orientation > Landscape
- On the ribbon choose Page Layout > Orientation > Portrait
- On the ribbon choose Page Layout > Size > A4
- On the ribbon choose Page Layout > Margins > Narrow
Print area, print titles and scaling#
- Select A1:F74
- On the ribbon choose Page Layout > Print Area > Set Print Area
- Python: code ps = ws.PageSetup ps.PrintTitleRows = '$3:$3' ps.Zoom = False ps.FitToPagesWide = 1 ps.FitToPagesTall = False
Page Break Preview and Page Layout view#
- On the ribbon choose View > Page Break Preview
- On the ribbon choose View > Page Layout
Headers and footers#
- On the ribbon choose Insert > Header & Footer
- Type
October sales report
- On the ribbon choose Header & Footer > Go to Footer
- Type
Page - On the ribbon choose Header & Footer > Page Number
- Type
of - On the ribbon choose Header & Footer > Number of Pages
- Click H10
The header shows October sales report on every page, and the footer shows the real page numbers, Page one of, and the total count. If more orders are added and the report grows by a page, the footer updates by itself.
- On the ribbon choose View > Normal
- Click A1
Locking and protecting#
Sheet: Quote
- Select B4,B6:B7,E4
- On the ribbon choose Home > Format > Lock Cell
- Python: code ws.Range('B5,B10:B15').FormulaHidden = True
- Ribbon: ok True, path Review > Protect Sheet
- In B4 type
120 - Click B15
The input was accepted and everything recalculated. Food cost is now six thousand six hundred, the subtotal seven thousand eight hundred, the discount three hundred and ninety, the total ex GST seven thousand four hundred and ten, GST seven hundred and forty one, and the total including GST eight thousand one hundred and fifty one dollars. And look at the formula bar while the totals are selected. It's empty. The formulas are hidden, but the results are still there.
- Double-click B14 to edit it
- Dialog ok: wait 2
- On the ribbon choose Review > Unprotect Sheet
Project: Printable tax invoice template#
Sheet: Customers
The customer list#
In A1 type
Customer listStarting in A3, type these rows (one value per cell, moving right):
|
Customer|Address|Suburb|Postcode|Contact| |---|---|---|---|---| |Bluewren Kitchen|22 Crown Street|Wollongong NSW|2500|Priya Shah| |Harbour Lights Bakery|5 Wharf Road|Kiama NSW|2533|Tom Nguyen| |Red Gum Diner|140 Princes Highway|Dapto NSW|2530|Sarah Kelly| |The Daily Grind|8 Market Lane|Shellharbour NSW|2529|Ben Rossi| |Seacliff Deli|31 Ocean Parade|Thirroul NSW|2515|Mia Clarke|
- Table: name Customers, range A3:E8
- Format A1: bold, font size 14
- Format B3:E8: AutoFit columns
- Format A3: column width 22
Invoice header and business details#
Sheet: Invoice
- Format A1: column width 44
- Format B1: column width 8
- Format C1:D1: column width 16
- In A1 type
TAX INVOICE - In A2 type
Saltbush Coffee Co.
Starting in C2, type these rows (one value per cell, moving right):
|
Invoice number|1045| |---|---|Starting in C3, type these rows (one value per cell, moving right):
|
Invoice date|13-Nov-2026| |---|---|Starting in C4, type these rows (one value per cell, moving right):
|
Due date|=D3+14| |---|---|Starting in C5, type these rows (one value per cell, moving right):
|
Terms|14 days| |---|---|Format D2: number format "INV-"0000
Format D3:D4: number format dd-mmm-yyyy
Format D2:D5: align right
D2 shows INV dash one zero four five, but it's still the number one thousand and forty five. The invoice date shows thirteen Nov twenty twenty six, and the due date is twenty seven Nov twenty twenty six, a Friday, exactly fourteen days later.
Bill to, with XLOOKUP#
- In A7 type
Bill to - Validation: error_message Please choose a customer. New customers go in the Customers table first., input_message Choose a customer from the list., list =INDIRECT("Customers[Customer]"), range A8
- In A8 type
Bluewren Kitchen
- In A9 type the formula
=XLOOKUP(A8,Customers[Customer],Customers[Address],"Choose a customer")
The address block fills in: twenty two Crown Street, Wollongong NSW twenty five hundred, and Attention: Priya Shah. The person filling in the invoice chooses one name, and three lines of address appear correctly spelled every time.
Line items and totals#
Starting in A13, type these rows (one value per cell, moving right):
|
Description|Qty|Unit price|Amount| |---|---|---|---| |Espresso blend beans, 1 kg|12|32.5| | |Decaf beans, 1 kg|4|34| | |Takeaway cups, sleeve of 50|20|6.8| | |Barista milk jug, 600 ml|2|24.95| |
- In D14 type the formula
=IF(B14="","",B14*C14) - Fill D14:D19 down (drag the fill handle, or select it and press Ctrl+D)
Three hundred and ninety, one hundred and thirty six, one hundred and thirty six, and forty nine ninety. Rows eighteen and nineteen look empty, but they hold the formula, ready for more items. The IF is there so the blank rows don't show zeros all the way down. That keeps the printed invoice clean.
Starting in C21, type these rows (one value per cell, moving right):
|
Subtotal|=SUM(D14:D19)| |---|---|Starting in C22, type these rows (one value per cell, moving right):
|
GST 10%|=ROUND(D21*10%,2)| |---|---|Starting in C23, type these rows (one value per cell, moving right):
|
Total due|=D21+D22| |---|---|In A21 type
Payment by bank transfer within 14 days.In A22 type
Please quote the invoice number with your payment.In A23 type the formula
="Please pay by "&TEXT(D4,"d mmmm yyyy")
The subtotal is seven hundred and eleven dollars ninety. GST is seventy one dollars nineteen, and the total due is seven hundred and eighty three dollars nine cents. And A23 reads: Please pay by twenty seven November twenty twenty six. Change the invoice date and this sentence changes too.
- Format C14:D19: number format currency
- Format D21:D23: number format currency
- Format B13:D13: align right
- Select A1
- On the ribbon choose Home > Cell Styles > Title
- Select A13:D13
- On the ribbon choose Home > Cell Styles > Heading 3
- Select C23:D23
- On the ribbon choose Home > Cell Styles > Total
- Format A2: bold, font size 12
- Format A7: bold
- Format C2:C5: bold
- Format C21:C23: bold
- Format A21:A23: italic
- On the ribbon choose View > Gridlines
That already looks like a real invoice. A clear title, the business details, the invoice number and dates on the right, the customer block, the line items, and the totals with a double line under the amount due.
Inputs, validation and a note#
- Select D2:D3,A8,A14:C19
- On the ribbon choose Home > Cell Styles > Input
- Format D2:D3: align right
Every place to type now has the soft orange fill and a border, including the two empty lines at the bottom, ready for more items.
- Validation: between [1, 1000], error_message Quantity must be a whole number from 1 to 1000., input_message Quantity: a whole number from 1 to 1000., range B14:B19, whole True
- Validation: between [0, 10000], decimal True, error_message Enter a price from $0 to $10,000, excluding GST., input_message Unit price excluding GST., range C14:C19
- Click D2
- On the ribbon choose Review > Notes > New Note
- Type
Add 1 to the last invoice number. Never reuse a number. - Press Esc
Print setup for one A4 page#
- On the ribbon choose Page Layout > Size > A4
- On the ribbon choose Page Layout > Margins > Normal
- Select A1:D23
- On the ribbon choose Page Layout > Print Area > Set Print Area
- Python: code ws.Range('D4,A9:A11,A23,D14:D19,D21:D23').FormulaHidden = True ps = ws.PageSetup ps.Zoom = False ps.FitToPagesWide = 1 ps.FitToPagesTall = 1 ps.CenterHorizontally = True
- On the ribbon choose View > Page Break Preview
- On the ribbon choose View > Normal
Lock it down#
- Select D2:D3,A8,A14:C19
- On the ribbon choose Home > Format > Lock Cell
- Ribbon: ok True, path Review > Protect Sheet
Using the template#
Starting in A18, type these rows (one value per cell, moving right):
|
Cleaning tablets, tub of 100|6|18.5| |---|---|---|
The amount is one hundred and eleven dollars. The subtotal rises to eight hundred and twenty two dollars ninety, GST to eighty two dollars twenty nine, and the total due to nine hundred and five dollars nineteen. Typing into the unlocked cells works perfectly, even though the sheet is protected.
- In A8 type
Red Gum Diner - Click D23
The address changes to one forty Princes Highway, Dapto NSW two five three zero, Attention: Sarah Kelly. And when I clicked the total, the formula bar stayed empty, because the formula is hidden. The amounts and totals are safe.
- Ribbon: ok True, path Review > Protect Workbook
Challenge#
Recap and what's next#
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



