Microsoft Excel - Managing Personal Finances with Excel

Set up the personal budget in excel by creating initial tables and formatting, open Personal Finances hyphen zero one file, and build monthly expense dashboard, expense tracker, and data tables.

Build a bills table in Excel to track due dates, expected amounts, actual payments, and the difference, powering a dynamic monthly expense dashboard.

Format a selected range as a table in Excel using the home tab, format as table, choose a style, enable headers, and resize with the corner handle.

Identify the bill's table by merging B12:F12 into a centered, bold header; set a prominent font size and plan additional tables later.

Select the merged header cell and apply a thick border to the top, left, and right. Choose colors and thickness to tie the header to the table.

Enter monthly expense records into a formatted Excel table, input rent and utilities details, and set up formulas to compute differences and track payments.

Apply table data formatting in Excel by formatting the bill amounts as currency, widening columns for readability, and exploring date formats to present dates clearly.

Learn to remove the filter button from a formatted Excel table, disable header drop-downs via the table design tab, and re-enable filtering later if needed.

Learn how to enable the Excel table total row and configure grand totals for columns like amount and actual, with automatic updates as you add or delete data.

Name the bills table in Excel to make formulas reference the correct data. Use the table design tab to set the name, avoiding spaces.

Build an expenses table and a subscriptions table in Excel to track budgeted versus actual amounts, with headers, total rows, merged titles, named tables, and turned-off filters.

Create and format expense tracker table in expense tracker worksheet, name it TBL expense tracker, add headers: expense, date, amount and description, and enable a total row with currency formatting.

Create a dynamic data validation dropdown in the expense tracker by pulling names from bills, expenses, and subscriptions using the tocol function on Microsoft 365.

Apply data validation as a drop-down list in the expense column of Excel, sourcing from a predefined list, then sort the list alphabetically with the sort function to streamline entry.

Learn how to create a dynamic data validation list in Excel using the OFFSET and COUNTA functions, starting at I3 and expanding as data grows.

Create a master list for data validation by pulling bills, expenses, and subscriptions into a pivot table and referencing it in the data validation settings.

Input and manage expenses in the expense tracker table, recording items like entertainment and car payment with dates and amounts, then compare actuals to the forecast on the monthly dashboard.

learn to build an actual spent formula in excel using the sumif function to pull real expenses from the expense tracker into the monthly expense dashboard, organized by category.

Learn to compute actual spending using the sum if function in an expense tracker, and calculate the difference between budgeted and actual amounts with a simple subtraction formula.

Create a pivot table from the expense tracker to summarize your budget across bills, expenses, and subscriptions, yielding a master summary of spend and its percentage of the grand total.

Create a pivot table in Excel, add a second sum field, and show values as percent of grand total to reveal each bill's share of expenses.

Move a pivot table from its own worksheet to the monthly expense dashboard using the analyze tab, placing it beneath the bills for a joined view of sums and percentages.

Format your pivot table to match the worksheet by applying currency formats, renaming fields, and controlling column widths, then preserve formatting on updates for consistency.

Create a visual representation of pivot table data by inserting a simple pie chart from the pivot chart, hide field buttons to reduce clutter, and quickly compare spending across categories.

Create a custom column chart that shows budgeted versus actual amounts for bills and expenses, using data from two tables and configuring the data series.

Create a customized pie chart to visualize actual spending by category (bills, expenses, subscriptions) using Excel, turning raw amounts into visual, decision-ready insights.

Format charts by adding titles, data labels, and legends; remove grid lines and borders; adjust font sizes to highlight budget versus actual in column and pie charts.

Explore creating calculated callouts in Excel by inserting shapes, placing text and a live calculation inside a shape, and referencing a cell for total expenses.

Create an income table in the dashboard with columns income name, date, amount, format as a table, add a total row named TBL income, and sum income to plan spending.

Create a dynamic callout shape in Excel that references the total income cell and concatenates text with the income value using ampersand, updating automatically.

Learn how to add a line break inside an Excel callout by using the CHAR(10) function and concatenation to display total income on two lines.

Learn how to format a numeric value as currency in Excel by applying the text function with a custom format code, displaying dollar signs, commas, and decimals in a callout.

Create a total spent callout by copying the income box, updating the cell reference, and composing a formula that sums bills, expenses, and subscriptions with a line break.

Create an income left box in Excel by subtracting total spent from total income, and align three boxes for equal spacing.

Apply Excel conditional formatting to highlight negative difference values in your bills table using a formula, manage rules, and adjust relative references to reflect each row.

Apply a workbook-wide theme to instantly update colors, fonts, and formatting in your expense tracker, using the page layout themes gallery to switch between theme colors.

Hide unused columns such as V, I, and L to keep the monthly expense dashboard clean, shielding formulas and dropdown helpers from users while they focus on the created tables.

Turn the monthly expense dashboard and expense tracker into a reusable Excel template by cleaning data, removing extras, and saving as a template in the office templates folder.

Learn to calculate monthly loan payments in Excel using the PMT function. See how rate, number of payments, and the present value determine the monthly payment and total interest.

Learn how to use Excel's FV function to project investment growth from an initial amount with monthly contributions, a 7% return, and terms up to 40 years.

Leverage Excel data tables to compare payment and future value scenarios, varying monthly contributions, rates, and terms with single and multi variable tables.

Download the completed Excel workbook from the resources to view the exact file built in the lessons, including the payment and investment calculators.