The HSTACK function appends ranges and arrays horizontally in one step, returning a single dynamic spill range that expands or contracts as sources change. If VSTACK is the vertical stacker, HSTACK is its horizontal twin, designed to line up multiple lists or tables next to each other without helper columns, copy‑paste, or manual resizing. The syntax is simple: =HSTACK(array1, [array2], …). Each subsequent array is placed to the right of the previous one, and the result spills into as many columns as needed. Microsoft’s function reference notes that HSTACK’s row count equals the maximum number of rows among the inputs, while the column count equals the sum of all input columns, with shorter arrays padded to match the tallest input. This behavior makes it ideal for consolidating period columns, stitching lookup results beside IDs, or building side‑by‑side comparison panels that stay in sync with source changes.

Read More

The EXPAND function lets Excel 365 users resize a returned array to exact dimensions and fill added cells with a value of choice. Think of EXPAND as the missing piece when building report‑ready ranges from dynamic arrays. It takes a source array, returns a version with the exact number of rows and columns specified, and pads any new cells with either a custom value or the default error. This makes it ideal for standardizing array sizes for charts, dashboards, and exports. According to Microsoft’s documentation, the syntax is =EXPAND(array, rows, [columns], [pad_with]) and it will return #VALUE if rows or columns are smaller than the source, and #N/A in padded cells if pad_with is omitted. It’s part of the dynamic array family and available to Microsoft 365 users on Windows, Mac, and the web as of recent builds.

Read More

When working with invoices, receipts, or sales reports, you may need to apply different tax rates depending on the product type, service category, or customer location. Excel makes it easy to handle multiple tax rates if you set it up correctly. I will show you efficient and accurate ways to manage tax rates in Excel.

Read More

Need to send professional invoices with tax calculated automatically? Excel is a powerful tool for small business owners and freelancers who need customizable, tax-ready invoice templates.

This step-by-step guide will show you how to create an Excel invoice template with automatic VAT or sales tax calculation built in.

1. Layout Your Invoice Template

Start with a clean layout. Use the top of the sheet for company details and the client’s information.

• A1–A3: Your business name, address, phone
• C1–C3: Client name and address
• A5: Invoice number
• C5: Invoice date

2. Create the Itemized Section

Add column headers:

• B7: Item Description
• C7: Quantity
• D7: Unit Price
• E7: Total (Quantity × Unit Price)

In cell E8, use this formula:

=C8*D8

Drag the formula down to support more rows.

3. Add Tax Calculation

Below the items list, include:

• Subtotal: =SUM(E8:E20)
• Tax Rate (%): Enter manually or link from another cell (e.g., F22)
• Tax Amount: =Subtotal * Tax Rate
• Total Amount: =Subtotal + Tax

💡 Example:

F22 (Tax Rate) = 0.20  (for 20% VAT)
F23 (Subtotal) = SUM(E8:E20)
F24 (Tax)      = F23 * F22
F25 (Total)    = F23 + F24

4. Make It Reusable

• Use data validation to prevent input errors in quantity and price
• Format totals as Currency
• Lock formula cells and protect the sheet
• Save as Excel Template (*.xltx)

5. Optional: Add Currency or Multi-Tax Support

For international use, add dropdowns to select currency symbols or multiple tax rates. You can use named ranges and VLOOKUP for dynamic tax handling.

With just a few formulas and formatting tweaks, you can build a powerful tax-ready invoice in Excel.

Want to allow edits in some parts of your Excel sheet while locking others? Excel gives you the power to lock specific cells without making the entire sheet read-only. This guide shows you how, step-by-step.

Read More

Want to make your Excel sheet cleaner and more dynamic? You can automatically hide rows based on specific cell values using filters, conditional formatting tricks, or simple VBA code. This tutorial will walk you through all the methods.

Read More

Formula errors are a common headache in Excel. Whether you’re seeing green triangles in the corner of a cell or getting mysterious error codes like #DIV/0! or #NAME?, this guide will help you understand and fix the most common issues.

Read More

Manipulating dates and times in Excel using VBA opens up a world of automation and custom functionality that goes far beyond what standard worksheet functions can offer. While Excel itself is quite good with dates (storing them as serial numbers), VBA gives you programmatic control to parse, calculate, format, and interact with dates and times in powerful ways. Let’s see how you can wield this capability.

Read More

Imagine you and your friends decide to rent a vacation home for a weekend. The total cost for the stay is $600. However, not everyone can stay for both nights. You need to figure out how much each person should pay, ensuring it’s fair, based on the number of nights they’re staying. This is a perfect scenario for using pro rata calculations, and Excel is our tool of choice to solve this puzzle.

Read More

Adding a zero before numbers or text in Excel is a common requirement, especially for data that needs to maintain a certain format, like ZIP codes, phone numbers, or numerical codes. Excel, by default, might not display leading zeros since it interprets the data as numbers. Here are several methods to achieve this, ensuring your data is displayed exactly as needed:

Read More