5 Simple Excel Tips & Tricks to Make Your Spreadsheets Work Smarter (Not Harder!)

As a small business owner it is likely you make use of Excel for storing information (referred to as data). But perhaps you aren’t an Excel or spreadsheet expert. This means you probably face some of the common struggles many of us face with messy data, clunky formulas, or sheets that feel more like a puzzle than a productivity tool.

In this article I highlight some of the many features and functions of Excel, that can help bring order to chaos.

My Excel Tips and Tricks:

1. Clean Up Your Data with One Click – Introduce “Remove Duplicates” and “Trim” functions for tidying up lists.

Use the “Trim” function to delete the unnecessary blank characters from a cell. Use of this function will return the text after deleting the unnecessary spaces.

To find out how to use the Trim function, refer to Microsoft support – Trim.

Remove duplicates – Used to identify duplicate data in a row or column.  When you use the Remove Duplicates feature, the duplicate data is permanently deleted. It’s always a good idea to copy the original data to another worksheet, before you delete the duplicates, so you don’t accidentally lose any information.

To find out how to use Remove Duplicates, refer to Microsoft Support – Remove Duplicates.

2. Use Conditional Formatting for Instant Insights – Show how colour coding can highlight overdue invoices, top clients, or low stock. Conditional formatting Automatically apply colours, icons, etc. to cells based on their values. This can help you identify patterns and outliers.  

To find out how to use Conditional Formatting, refer to Microsoft Support – Conditional Formatting.

3. Turn Data into Dashboards (Without Complex Formulas) – A teaser on pivot tables, showing how they summarise data quickly.

In day to day business, analysis is very important factor to run a business successfully. Analysis helps us in getting a clear idea of performance, trends, and aids decision making, to identify when things need to be changed. If you could add a dashboard onto the front of your spreadsheet, this may be a game changer for you.

Pivot tables analyse small sections of large data sets. To create a pivot table, simply select the columns and rows you want to evaluate.  Pivot tables are easy to create, and can instantly give you insight into the data / information in your spreadsheets.

To find out how to use Conditional Formatting, refer to Microsoft Support – Pivot Tables.

4. Speed Up Everyday Tasks with Shortcuts – Here a few handy keyboard shortcuts (e.g., Ctrl + Shift + L for filters).

Shortcut keys help to provide an easier and quicker method of operating Microsoft Excel. Shortcuts can save much time, by not using the mouse clicks. Keyboard shortcuts in Excel are commonly accessed by using the ALT, Ctrl, Shift, Function key and Window key.

Here is a list of the ones I use most frequently:-

Other useful shortcuts I like include:-

• Format Painter – Copy and paste the format from one cell to another. This is a quick way to format cells with the same colours, fonts, and format.  
• Autofill – Automatically fill in a series of repetitive data. For example, if you want to fill in a series of dates, start typing one date, then move the cursor to the lower-right corner of the last cell.  
• Flash Fill – Automatically detect patterns in your spreadsheet and fill in the remaining cells.  
• Text to Columns – Separate data into multiple columns. For example, if you have a column of names, you can use the Text to Columns feature to break them out into first and last names. 

5. Improve data quality in spreadsheets with fields where the content is controlled by List (or ‘Combo’) Boxes.

This is especially useful for spreadsheets that are shared. You can make data entry easier, by letting users choose a value from a combo box. 

To find out more about list / combo boxes visit Microsoft Support – Add Combo Box.

If your spreadsheets feel more frustrating than functional, a quick refresh could make all the difference. My Spreadsheet Spring Clean is designed to help business owners like you tidy up, organise, and streamline their data—without the overwhelm. Book a free 30-minute consultation call, and let’s get your spreadsheets working for you!