Excel for urban planners on Mac: formulas, pivot tables, and limits in Microsoft 365

You do not need Excel to make maps. The maps come from GIS, the parcel data comes from the county assessor, and survey answers come from a form. Excel sits in the middle. It is where you filter a parcel export, count units by zoning district, and check the math before a number reaches a staff report.

City Data Intelligence calls Excel the most universally used data tool in local government. It credits Excel's familiarity, accessibility, and flexibility for ad hoc analysis. Those are the reasons most planners keep a workbook open all day, even when a bigger system exists.

Limits you hit on a Mac

Excel handles a lot of rows, but it has a ceiling. One worksheet holds 1,048,576 rows and 16,384 columns, according to Microsoft. A county parcel file can pass that line. Rows past the limit do not load, so check the count before you open the file.

Speed shows up first. City Data Intelligence says a dataset of more than 100,000 rows slows Excel to a crawl. You feel it in filters, lookups, and pivot refreshes. Move the heavy joins out of the workbook when the file gets this big.

Your version matters. A 32-bit copy works within the 2 GB process limit. A 64-bit copy has no fixed file size cap, and memory becomes the constraint. Open Excel > About Excel to see which build you run.

Power Query on Mac: what works and what does not

Power Query pulls data from files and tables, then cleans it before it lands in a sheet. This is useful when a county sends a new export every quarter. The Mac version covers less ground than the Windows version, so know the gaps before you build a workflow around it.

Microsoft says authoring in the Power Query Editor is not available in Excel for Mac. You can still open the editor to shape data. The shaping editor is generally available to Microsoft 365 subscribers on version 16.69 (23010700) or later.

Import sources on Mac include Excel Workbook, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online List, OData, Blank Table, and Blank Query. The SQL Server source appears only in Insiders Beta. Other databases need an ODBC driver on the Mac.

Practical rule: use Power Query on the Mac to open and reshape a CSV that arrives on a schedule. If a query needs new steps, ask a colleague on Windows to author it. Microsoft says moving a VBA module from Windows to Mac takes two steps, so test it before the deadline.

Pivot tables for zoning and land use counts

Pivot tables are the fastest way to count units by district. They also break quietly when the source data is messy. Start with a clean table, then build the pivot from it.

1. Click any cell in the parcel data. Press ⌘ T to turn the range into a table.
2. Name the table as you create it. Microsoft's October 2026 update says that option now reaches Excel for Mac.
3. Go to Insert > PivotTable, and pick the table name, not a cell range address.
4. Drag Zoning District to Rows and Units to Values. Set the Values field to Sum.
5. Drag Jurisdiction to Filters, so one file can answer many questions.

Common mistake: a unit count stored as text. The pivot then counts rows instead of adding units. Select the column and check the number format on the Home tab before you build the pivot.

Another trap is a blank row inside the table. Excel stops the table at the blank line, and the pivot misses everything below it. Delete the gap, then refresh with Data > Refresh All.

For the full walkthrough, see how to make a pivot table in Excel on Mac.

Formulas for cleaning parcel and permit data

Most planning workbooks need a handful of formulas. These cover the usual cases in a county file.

• TRIM removes extra spaces. Use =TRIM(A2) when an export adds trailing spaces to parcel IDs.
• COUNTIFS counts rows that meet several conditions. Use it to count approved permits in one district.
• SUMIFS adds a column only where the conditions match, such as total units in one zone.
• XLOOKUP matches a parcel ID from one table to another. It ships with Microsoft 365, so older Office builds will show an error.

Leading zeros are a trap. Excel drops the zero in a parcel ID like 0012345 when it reads a CSV as numbers. Format the column as Text before you import, or set the type to text in Power Query.

Logic buried in cell formulas is hard to audit. City Data Intelligence names this as a common pain point. Keep a notes tab that records each formula's purpose and the date you last checked it.

Worked example: a zoning capacity check

Say the planning commission asks how many units a district could hold after a rezoning. Work through it in this order. The steps are simple. The mistakes are in the inputs.

1. Put lot area in one column and allowed density in another. Confirm both use the same unit, such as acres or square feet.
2. Add a helper column that multiplies lot area by density. Format it as Number, not Text.
3. Add a constraint column. Lots with a recorded wetland or easement flag get a zero.
4. Sum the helper column with SUMIFS, so only eligible lots count toward the total.
5. Paste the total into a notes tab with the source file name and the export date.

The usual mistake is applying density to gross acreage. Many codes measure net acreage, which removes streets and constraints. Read the ordinance text before you pick the column.

Then check your total against the GIS count. If the two differ, the gap is usually a lot that failed the constraint test, or a mismatch in the join. Do not trust the Excel total until you can explain the gap.

Survey data, public input, and forms

Public outreach often starts with a form. APA Planning says planners can build surveys with Excel-based templates called XLSForms, using ODK, an open-source tool. They then deploy the form to phones with ODK Collect. The spreadsheet becomes the form design, so the logic lives in one place.

When the responses come back, export them to Excel. Rename headers to plain words, remove test entries, and keep the raw export untouched in its own tab. Work only on copies after that.

Monthly reports that rebuild every cycle

A report built by hand gets rebuilt each cycle. City Data Intelligence lists this as a common pain point for planning offices. You can cut the rebuild time by fixing the structure of the workbook once.

• Keep one raw data tab per cycle. Never type over it.
• Make each raw tab a named table, so formulas read the table name instead of a cell address.
• Build the summary tab with pivots or SUMIFS that point at the table names.
• Save a PDF copy for the commission packet, and keep the workbook as the source.

To save the PDF, use File > Save As and choose PDF in the format menu. Keep the workbook next to it, so a reviewer can trace each number back.

Version and file risks to check now

Old Office installs cause real trouble. TidBITS reported that Office 2019 for Mac would go read-only on 13 July 2026. In that mode you can view and print, but you cannot edit, save, or create new files.

TidBITS readers reported that some copies still saved files after that date, and that license checks may happen only monthly. Those are reader reports, not confirmed facts. Do not rely on them for a commission deadline.

Check your build under Excel > About Excel. If you still run Office 2019, move the active workbooks to a supported Microsoft 365 install before the next hearing.

Where Lazur helps inside Excel

Lazur is a voice assistant for the Mac. It lives in the notch. Hold ⌥ ⌘ to ask about anything on screen, and Lazur answers out loud. It listens and looks only while you hold the shortcut.

Ask where something is, and Lazur draws a circle and a label around it. This helps when a ribbon layout changes between versions. Ask how to do something, and Lazur guides you one click at a time, waiting for each click.

Lazur does not click buttons, edit files, or operate other apps for you. It points and explains. Your hands do the clicks, and the workbook stays yours.

Hold fn to dictate text at the cursor. Dictation works well for the notes that sit beside a workbook, such as a caveat or a line for the staff report. Audio and the screenshot go to AI providers to answer you. They are not stored, and content is not used to train AI models.

When Excel is the wrong tool

Excel is good for the middle of the job. It is weaker for spatial work and for large automated pipelines. City Data Intelligence recommends Python or SQL for large or automated work.

Use GIS for geometry, such as parcel overlap, buffers, and distance to transit. Use Excel for the counts that sit on top of that geometry. Move data between them through a clean export, not a copy and paste from a map screen.

If a workbook slows down every time you refresh it, move that step out. A script or database can run the heavy part, and Excel can keep the summary.

Next steps for your Excel workflow

Start with one workbook you already maintain. Check its row count against the limit. Replace one hand-typed total with a formula. Name the table, then build one pivot from it.

If you want a voice helper while you work, download Lazur for the Mac. Trial and plan details are on the pricing page. For Excel-specific help, read the Excel page.