5 ways Excel can help automate your business processes
Microsoft Excel has become a cornerstone in our daily work lives. When used to its full capacity it can streamline those labour intensive processes that have previously required hours of repetitive, monotonous inputting. Here are 5 ways you can use Excel to automate some of your business processes and optimise your time.
- Power Query
Do you have multiple spreadsheets for your figures and are having to copy and paste data every time? Did you know the answer was just a tab away? Power Query, a free extension to Excel, allows you to connect to many different datasets, transform, and combine them (among other things!). It has a significant processing power, while Excel sheets are limited to 1m rows. As such, Power Query can be used to, for instance, consolidate your sales figures from different time periods or regions. Additionally, queries do not need to be “outputted” to an Excel sheet – they can be stored in Power Query and used for further transformations.
- Macro Recorder
If you lack VBA skills, but would like to automate a repetitive step you perform on your dataset, consider Macro Recorder. The tool allows you to create a macro by simply showing Excel what you want it to do – no coding required. You can then add a button to your spreadsheet and assign the macro to it so that the action is automatically performed every time you click! Macro Recorder can be used for moderately complex tasks. For example, if you need to change a name in your dataset every time it’s refreshed, you can record a macro that will do that for you in a blink of an eye! If you require to automate tasks of more complexity, you can create a macro manually using VBA, however, this involves some more expertise.
- Data Analysis ToolPak
Another great add-in for Excel is Data Analysis ToolPak. It allows you to perform detailed analysis on your data, such as assess correlation, regression, or covariance. You can also use it to generate random samples or view descriptive statistics about your data. As such, the tool can be used to, for example, explore whether your marketing spend corresponds to increased sales in one-click! Of course, some of the analysis can be performed without the Data Analysis extension by using formulas, however, the extension will perform the task significantly faster and ensure no errors sneak in.
- Formulas
If you’re not comfortable using VBA or Power Query, formulas are still a great way to automate your spreadsheet and generate valuable insights. In particular, using a combination of complex formulas can transform your dataset in an efficient way and save you time (but remember not to overcomplicate things!). For example, LET function allows you to define a variable which can be subsequently used throughout the formula (but not anywhere else in the spreadsheet), where the variable is repeated multiple times. It can define a variable which needs to be looked up from another sheet and assign it a name, which makes the formula read much more easily. For lookups, we recommend a combination of INDEX and MATCH functions or XLOOKUP, which is an improved version of VLOOKUP. Other useful functions to remember are DATEVALUE, SUMPRODUCT, IFERROR, TRIM or CONCATENATE. A combination of some of the above functions could, for instance, help you design a spreadsheet to track project spend.
- Pivot Tables
Pivot Tables might be a well-known feature of Excel used by many, however, not everyone knows of some of the complex tasks it can perform! For example, did you know it was possible to create calculated fields within Pivot Tables? Or group data within a Pivot Table’s column into groups? Such features can be used to view your data in greater detail and generate additional insights. As a result, the process of analysing your dataset can significantly improve and allow fast and easy decision making. Additionally, using Pivot Tables will save you time and effort. You can use it, for example, to quickly summarise and analyse your sales figures.
AUTHOR
Barbara Leska and Jago Sincock-Ford
Consultant & Analyst