The Excel Users Guide to Microsoft Access - Manage & Reports

Download the course materials to start your journey with the Excel users guide to Microsoft Access, focusing on managing and reporting.

Learn to build flexible Excel reports from a monthly data dump, separating balance sheet and profit and loss data, filter by chart of accounts, and prepare data for Access integration.

Create a unique key field by concatenating company, branch, department, main account, and sub account, then import these files into Access for monthly reporting.

Discover how to organize data in Access with separate databases, learn the five Access objects, and import data from Excel to create queries, forms, and basic reports.

Build a comprehensive lookup table with a key field as the common denominator to capture all accounts and descriptions from the beginning of time until this month for flexible reporting.

Compare monthly accounting data with the lookup table to ensure every unique account value exists, use the find unmatched query wizard to identify gaps, then append missing accounts.

Copy the lookup table to Excel, add new account records, and append them back into Access to update the lookup table, then run a query to verify.

Build an Access query from scratch in design view, linking a lookup table to January 2016 and January 2015 to compute current month, year-to-date, and budget data for Excel reporting.

Learn to build a balance sheet query in Access using the unmatched query wizard and design view, linking fields, focusing on year-to-date 2016 balances, with import/export steps and validation.

Manage data without access by building a lookup table in Excel, creating monthly templates, and performing lookups to replicate Access queries and generate reports.

Export details from access into Excel and build monthly financial reports using the PNL reports file; keep 12 months of data, align headings, freeze panes, and refresh with a click.

Create a first pivot table report in a new worksheet to summarize company financials using calculated items for net sales, gross profit dollars, gross profit percent, and operating income.

Craft a report in Excel using pivot tables, with headings current month to date and year to date, and calculated fields for month to date and year to date changes.

Reuse a consolidated report to compare actual versus prior period and actual versus budget. Create two calculated fields and drag fields to update current month-to-date and year-to-date budgets.

Create consolidated company reports by duplicating pivot table views, filter by company name, and display actual versus prior against all financial statement categories for each company.

Learn to set subtotals to none in the company name field to remove irrelevant totals in a pivot table, improving the company report.

Master branch level reporting by creating a by-branch pnl view, filtering out non profit centers, and using conditional formatting with color scales to show year-over-year changes.

Create a base pivot report to analyze P&L by account, copy for new periods, filter by branch and month, clean categories, and apply conditional formatting for clear insights.

Learn to build monthly financial reports in Excel using pivot tables, link data to pivots, automate calculations, and refresh formats for consistent actual vs prior and year-to-date insights.

Apply variance analysis with pivot tables to compare net sales by branch for the current month versus prior period, calculating percent change and refining reports with subtotals and margins.

Explore a monthly variance analysis workflow in Excel, comparing actuals to prior periods and budgets, filtering by branch and highlighting changes with conditional formatting.

Explore divisional reports snapshot by building pivot-table analyses of net sales, gross profit, and gross profit percent by branch and department, using Access data and classic pivot table layout.

Export balance sheet details from Access to Excel, align with the key field, and build two years of monthly figures for streamlined balance sheet reports.

Explore monthly balance sheet trends by using a rolling 12-month trend report aligned to account categories in a pivot table, update the data source, and recalculate the percent change.

Explore building a year-over-year comparative report in Excel, comparing 2016 to 2015 by year in each row and filtering by month, with notes on manual calculations versus calculated items.

Provide an offer code that gives all students a 50 percent discount on every class to thank them and encourage continued learning.