Small businesses rely on spreadsheets for accounting, cost tracking, business analytics and various other functions. They use templates that often can't be customised too much.
To help business professionals optimise their speed of work and improve processes, we asked business leaders for their best Excel advice.
Here are 10 top Excel tricks for small businesses:
Connect Canva and Excel
Save Time With Find and Replace
Hide Columns and Sheets
Watch for Trends With Sparklines
Leverage Flash Fill
Make Filters a Thing of the Past
Use Pivot Tables for Analytics
Learn Data Validation Techniques
Create Excel Dashboard Reports Using PowerPivot
Why is the IF function so useful?
Connect Canva and Excel
Canva has a helpful integration with Excel. When I want to use Canva to show data on a graph, I input the data into the Excel spreadsheet. With the click of a button, Canva displays the data from the Excel spreadsheet on a graph in the Canva platform.
The graph can be a bar graph, a pie graph, a line graph, or a multitude of additional choices. Visuals help boost your Google rankings and user experience. Using Canva in conjunction with Excel is my favourite Excel tip.
Janice Wald, Mostly Blogging
Save Time With Find and Replace
Find & Replace - This shortcut allows you to replace multiple text or numbers with one click, press Ctrl+H, or go to Home > Find & Select > Replace. You'll have the option to solely replace one block or change all the blocks that match the old text or number to the new one.
You can also choose to replace the text in one sheet, multiple sheets, or within a specific range. I work with a large database, and this shortcut saves me so much time whenever I need to replace or edit a variable.
Jessica Ulloa, MyPerfectResume
Hide Columns and Sheets
One of my favourite tips that save me time and make my sheets more organized is hiding specific columns and sheets that I may not be using at the time or at all. This makes everything more organized, so I can focus on only the information needed. Also, I do not have to delete information if I don't currently need it, but I am not sure if it will be useful in the future!
Jonathan Finegold, MedCline
Watch for Trends With Sparklines
Learning how to use data within rows to create a sparkline can be really useful. A sparkline is basically a line graph that reflects trends in data. The line will go up or down depending on how the data is progressing over time.
All you have to do is select the rows from which you want the data to create your sparklines. From there, you’ll have the option to click a “Quick Analysis” button (or CTRL+Q). Then you’ll be given a few options, including “Sparklines,” which you can click.
You’ll then see sparklines at the end of each row which shows visual data trends corresponding with these rows. This is a helpful way to visualize your data trends rather than having to take more time looking at all of the numbers one by one.
Joaquín Roca, Minerva
Leverage Flash Fill
Whenever I have two columns of names and need to generate email addresses from all of them, do that for the first row, and Excel will figure out what you're talking about and complete the rest for you.
It was feasible before 2013, but it required a mix of functions. It is a lot quicker now, and it impressed me. After enabling the Flash Fill to form Advanced File options, you may start it manually by selecting Data, then Flash Fill, or pressing Ctrl E. It should begin working as soon as you start typing.
Axel Hernborg, Tripplo.com
Make Filters a Thing of the Past
Everybody knows about filters and the wonders they can do when sifting through a lengthy data set. While filters seemed to be the common practice, PivotTable Slicers serve the same purpose but have a completely different look.
Rather than have the dropdown menu option that a filter would have, slicers offer the user buttons that they can click on to be filtered by. Not only does it make it easier to choose what to filter by, but it also shows the user what's currently being filtered, so they always know what is and isn't being included.
When showing sets of data to your boss or potential clients, slicers make it simple to filter through different pieces of data and will make you look like a pro. Not only does it make you look good, but it saves energy and time that would normally be spent on making sure your filters are in place or when making sure that certain filters aren't already in use. All in all, slicers can make your use of filters a thing of the past.
Kate Lipman, embrace Scar Therapy
Use Pivot Tables for Analytics
One of my favourite functionalities within Excel is the pivot tables. These puppies can quickly show you mouth-watering morsels of data you'd never discover otherwise. So it's no secret that if you use them often, a nifty shortcut might be nice. If you want to save about 3-5 seconds here it is: Simply click Alt + N + V and enjoy! Warning: This is a sequential shortcut, so don't hold any buttons, but rather click them one after another.
Peter Bryla, ResumeLab
Learn Data Validation Techniques
Data validation is one of the most underused yet advantageous features of Excel. Excel offers several workflow perks via data validation tweaks, from predefined formulas to customized solutions with new worksheets. Prevention of duplicate values and creation of dropdown and even dependent dropdown lists come in handy all the time. Excel also allows you to set up preconceived criteria for simple functions such as days or dates and numeric or text values. For more adventurous users, data validation techniques offer you slightly more complicated features such as creating error messages that provide users tips and solutions on moving forward.
Krista Haws, Dripped Coffee
Create Excel Dashboard Reports Using PowerPivot
Since creating pivot tables based on data takes half an hour or more to load into Excel, you can speed up this process and have accurate results within seconds. The secret is in pre-calculating all the aggregated information you need for your pivot table beforehand and building a cube with it.
This cube will contain calculated columns with summarized values of your original data sources and therefore allows building a pivot table in seconds. The method is well known in Analysis Services (SSAS) development for building cubes, but you can also apply it to Excel data sources. It is a powerful hack that can save hours when you have many pivot tables to build, and it's easy to implement.
Sam Santa, Zeitholz
Why is the IF function so useful?
When we started our business, learning to use Excel’s IF function made a world of difference. For any business too small to have software to automate processes, using the IF function automates many of these within Excel, such as budgeting or tracking active deals and accounts.
For budgets, =IF(C2>B2,“Over Budget”,“Within Budget”) will automate it, telling you if you are within your budget for each item at a glance with no mental math or manual input.
Jason Kudlock, Mrs. Property Solutions
Terkel creates community-driven content featuring expert insights. Sign up at terkel.io to answer questions and get published.