Build Excel Spreadsheet for Work From Home Business Accounts

Learn to create and rename a new Excel worksheet tab, apply a consistent month naming pattern, and generate a title using a formula that references another worksheet.

Add a top section to the accountancy worksheet showing monthly incomings, outgoings, and running and monthly balances, using color panels, bold headers, borders, and merge and center for clear totals.

Create a reference sheet to populate dropdown lists in monthly worksheets, organizing two columns (transactions and analysis) and including bank receipts, payments, income, reimbursements, and expense categories.

Set up a nested if formula triggered by transactions to post receipts as positives and payments as negatives, using absolute references and currency formatting to secure reliable current account values.

Learn to copy and paste the transaction formula across other accounts in your worksheet, update references with absolute referencing for each account, and test the formulas to ensure accuracy.

Set up running balances in an accountancy worksheet by linking opening balances from the setup sheet and monthly balances, then test adjustments across months.

Learn how to use conditional formatting to change the font color of cells with negative numbers in running balances, signaling overdrafts in a work-from-home excel spreadsheet.

Learn to duplicate a worksheet while preserving formatting using move or copy, rename to May, and update formulas and running balances across all sheets for consistent monthly data.

Learn to update the month-year and running balances formulas in Excel by referencing the current month sheet (not the setup sheet), advancing from April to May and carrying balances forward.

Jennifer Bailey shows how to set up end-of-year worksheet with income and outgoings tables, headings auto-filled from a reference sheet, convert to tables, and add year totals, excluding national insurance.

Set up the outgoings table in the end-of-year spreadsheet using sumif to pull monthly expenses from the analysis column into the amount column, then apply currency formatting and totals.

Set up a totals table in Excel to calculate profits using income minus outgoings, then convert the table to a normal range and format the results as currency.

Fix autofill by enabling the fill handle in Excel via File > Options > Advanced and turning on Enable fill handle and drag and drop, then verify it works.

Learn how to fix hashes appearing in columns by expanding column width to fit data, using auto-fit via double-click between headers or by dragging; rows adjust similarly.

Jennifer Bailey explains why Excel auto-populates bottom cells with auto sum. She highlights the extend data range formats and formulas setting and the last-rows formula rule.

Learn how to create multiple worksheets, rename them, and color-code tabs to label months like January, February, and March in your workbook.

Change the color of your worksheet’s grid lines in Excel by using page layout and sheet options, then adjust the grid line color and enable print to preview colored lines.

Learn to change text color and cell fill in Microsoft Excel using the home tab, font color, and fill options, with dark blue text and red cells.