Tech Tip Tuesday: Quick Tricks with Excel. Flash Fill, Data Validation and Pivot Tables
Three powerful Excel features that help you clean, control and summarise your data faster
We begin today’s Tech Tip Tuesday with an overdue apology. This one is late. The last one was late. And the one before that may or may not have even gone out at all.
But in good news, that is because I have been fortunate enough to take on some new clients and have been spending more time solving real world problems with the tips I am sharing here.
Even better, we are now moving into a topic that genuinely excites me. Excel.
As an accountant by background, I consider Excel one of the most powerful and underused tools in any business. This week we are diving into three of my favourite features that can save time, improve data quality, and make you look very sharp in a spreadsheet.
Flash Fill. Let Excel Guess What You Mean
Flash Fill helps you automatically extract or combine data without formulas. You simply start typing what you want, and Excel figures out the pattern.
Example
You have a column of full names. You want to extract first names.
In the next column, type the first name from the first row
In the next row, start typing the second first name
Excel will offer a preview of the rest
Press Enter to apply the changes
Flash Fill works well for splitting text, joining fields, formatting dates or phone numbers, and more.
Data Validation. Keep Your Spreadsheets Clean
Data Validation lets you control what can be entered into a cell. It is perfect for dropdown lists, number limits, and preventing typos.
Example
You want users to select a status from Approved, Pending or Rejected.
Select the range
Go to Data, then Data Validation
Choose List
Enter the values
Now users can only select from your list
This keeps your data consistent and prevents problems later on with filters, charts or reports.
Pivot Tables. Make Your Data Work Harder
Pivot Tables turn messy data into clear summaries. They are easy to build and update, and they help you answer questions quickly without writing complex formulas.
Example
You have a list of sales by customer and region. You want to summarise total sales by region.
Click anywhere in the data
Go to Insert, then Pivot Table
Choose where to place it
Drag Region to Rows and Sales to Values
You now have a clean summary, and can filter, group and explore from there.
Pro Tip. Use All Three Together
Flash Fill helps you clean and format raw data
Data Validation keeps your input clean and consistent
Pivot Tables help you summarise it and find meaning
Together, they give you control from start to finish.
Excel is not just for accountants. But yes, it is where accountants feel most at home.
See you next Tuesday. Possibly.