Ways to automate repetitive spreadsheet work in Excel and Google Sheets without writing code: tables, Flash Fill, data validation, conditional formatting, Power Query, imports, forms, macro recorders and notifications.
If you do the same spreadsheet task every week, there's probably a way to automate it, often without writing any code. These built-in features save time and cut mistakes.
1. Excel Tables
Select your data and press Ctrl + T to turn it into a Table. Formulas fill down automatically, formatting extends to new rows, and charts and pivot tables based on the table pick up new data. Formulas can use readable names such as =SUM(Sales[Amount]). Google Sheets has a similar Tables feature (Format, then Convert to table).
2. Flash Fill and Smart Fill
Type an example of the result you want, such as a first name extracted from a full name, and Excel's Flash Fill (Ctrl + E) completes the pattern. Google Sheets offers Smart Fill suggestions. Check the results, as patterns can be misread.
3. Data validation
Stop errors at the source by restricting inputs: drop-down lists, date ranges, or whole numbers only. In Excel, use Data, then Data Validation. In Sheets, use Data, then Data validation, or insert a drop-down chip.
4. Conditional formatting
Automatically highlight overdue dates, low stock, duplicates or values above a target. A formula-based rule, such as highlighting rows where the due date is before TODAY() and status isn't "Done", turns a sheet into a live dashboard.
5. Power Query (Excel)
Power Query, under Data, then Get Data, imports and cleans data from files, folders, databases and websites. You record cleaning steps such as removing columns, splitting text and changing types, and next time you click Refresh to repeat them all. Combining every CSV in a folder becomes a one-click job.
6. Imports in Google Sheets
IMPORTRANGEpulls data from another spreadsheet;IMPORTDATAloads a CSV from a URL;IMPORTHTMLloads tables or lists from web pages;GOOGLEFINANCEfetches market data (with delays and limitations).
These refresh periodically, so your sheet stays up to date.
7. Forms feeding sheets
Google Forms can send responses straight into a sheet, and Microsoft Forms can connect to Excel for the web. Combine with formulas and conditional formatting for a simple tracking system, such as bookings or equipment requests.
8. Dynamic array formulas
In Microsoft 365 and Google Sheets, functions like FILTER, SORT, UNIQUE and XLOOKUP return results that update automatically. A single =FILTER(Orders, Orders[Status]="Open") replaces manual filtering and copying.
9. Pivot tables
Pivot tables summarise thousands of rows in seconds. Point them at a Table or whole columns, then refresh when data changes.
10. Macro recorders
Both apps can record your actions and replay them. Excel's desktop macro recorder writes VBA, and Excel for the web's Automate tab records Office Scripts. Google Sheets' recorder (Extensions, then Macros) creates Apps Script. You can use them without reading the code, though recordings may need adjusting if your data layout changes. Only run macros from sources you trust.
11. Notifications
Google Sheets can email you when changes are made (Tools, then Notification settings). Excel files in OneDrive or SharePoint can be connected to Power Automate flows, for example to send an email when a row is added, using templates without coding.
Where to start?
- List the tasks you repeat weekly.
- Turn your data into Tables.
- Add validation to inputs.
- Replace manual filtering with FILTER or pivot tables.
- Try Power Query or imports for data you copy in regularly.
Frequently asked questions
Is Power Query in Google Sheets?
No. Sheets uses import functions, connected sheets and add-ons instead.
Are macros safe?
Your own recorded macros are. Macros in files from unknown sources can be malicious; Microsoft blocks internet macros by default.
Sources and further reading
- Microsoft Support: Excel help and learning
- Microsoft Support: About Power Query in Excel
- Google Docs Editors Help: Google Sheets
- Microsoft Learn: Macros from the internet are blocked by default
Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.