For lots of people in the workplace, there are few things that strike fear into their hearts more than being asked to do something that involves Microsoft Excel. From the endless columns and rows of numbers to the impossibly-complicated formulas, it’s easy to lose a whole afternoon in spreadsheet hell and emerge somehow further away from the finishing line than when you started.
However, Excel has been around since the 1980s for a reason and that is that it’s incredibly useful and surprisingly flexible. Not only can it be used for all the tasks you currently use it for, but there’s a whole host of essential business processes that you could be running through it. Of course, for all of these, there’s special software out there that will do it for you, but these come at a high price and maybe aren’t really needed for smaller businesses.
That’s where Excel can come in.
From accounting to project management to HR and payroll, there’s so much that Excel can be doing for you if you know how and this guide will show you what you need to do. So here are all of the ways that your business can use Excel:
All you need to include is your monthly opening balance, income, expenditure and closing balance and you’ll be able to track your cashflow across months and spot any worrying trends before they can become a problem. You can also create a profit and loss statement in Excel by inputting the total budget, revenue, costs and taxes. Meanwhile, invoices are also easy to create a standardized template for if you want to keep your financial processes in Excel rather than having Word documents floating around too.
It’s not just payroll functions that can be transferred to Excel either. It’s important when running a business to know when your staff are going to be on vacation and having that in a single view helps to track busy periods to ensure you’re not understaffed. Of course you can pay for a SaaS solution to do this, but you can also do it yourself very easily in Excel with a vacation planner, while you can also use timecards to track their worked hours for overtime and sick pay purposes, all of which can go into the payroll calculator mentioned above.
Progress isn’t all you need to be able to track for a project either, and budgets can also be managed and forecasted in Excel, from the start dates to the end dates with materials tracked against the actual costs of the work being done. When projects start to overrun, you need to keep a close eye on what impact this is having on the budgets, but you don’t need to be spending extra on software to do so, because this humble spreadsheet can do it all for you.
Another potentially costly business tool is a CRM, with many solutions out there for you to try, but for smaller businesses who don’t have thousands of contacts to update on a daily basis, the question is whether you actually need to pay for one. Excel can work as a CRM for you with all of the lead information you need easily accessible and editable, particularly if saved on a tool like Sharepoint for multiple users to be able to gain access to at the same time.
You can also track and forecast the depreciation in value of your equipment or inventory over time, using the straight-line method to see the correlation of its useful life span and its salvage value and cost. Another usage is to track the inventory in a warehouse, by including and updating figures for unit prices, quantity in stock, reorder levels, reorder time in days, etc, which gives you a good overview of when new items need to be ordered.
Read next: What type of problem solver are you? (infographic)
However, Excel has been around since the 1980s for a reason and that is that it’s incredibly useful and surprisingly flexible. Not only can it be used for all the tasks you currently use it for, but there’s a whole host of essential business processes that you could be running through it. Of course, for all of these, there’s special software out there that will do it for you, but these come at a high price and maybe aren’t really needed for smaller businesses.
That’s where Excel can come in.
From accounting to project management to HR and payroll, there’s so much that Excel can be doing for you if you know how and this guide will show you what you need to do. So here are all of the ways that your business can use Excel:
Accounting and Finance
Anyone who works in accounting and finance will already be well-used to this particular piece of software, even if they have more powerful tools they might use for some of the most complicated processes. However, they might not be aware of exactly everything that Excel can do for them with the right templates and formulas. For example, it’s surprisingly simple to create an effective cash flow forecast spreadsheet, an incredibly important tool for any small business that wants to avoid running into money troubles.All you need to include is your monthly opening balance, income, expenditure and closing balance and you’ll be able to track your cashflow across months and spot any worrying trends before they can become a problem. You can also create a profit and loss statement in Excel by inputting the total budget, revenue, costs and taxes. Meanwhile, invoices are also easy to create a standardized template for if you want to keep your financial processes in Excel rather than having Word documents floating around too.
HR and Payroll
Speaking of financial processes, there’s few more important than payroll, without which you’d run out of staff very quickly. So it’s not a part of the business with much room for error, which is why most larger companies rely on specialized software, but if you have a smaller number of staff on your payroll, Excel has the tools for you. With your employees’ wage and tax information, vacation, overtime and sick pay details, it can calculate what they should get paid each month. It can even use that information to create paystubs for you to use.It’s not just payroll functions that can be transferred to Excel either. It’s important when running a business to know when your staff are going to be on vacation and having that in a single view helps to track busy periods to ensure you’re not understaffed. Of course you can pay for a SaaS solution to do this, but you can also do it yourself very easily in Excel with a vacation planner, while you can also use timecards to track their worked hours for overtime and sick pay purposes, all of which can go into the payroll calculator mentioned above.
Organization & Project Management
You can also use the timecard templates for project management purposes, tracking how much time has been spent on tasks, whether to track for internal reasons or to help accurately charge clients for work completed. There’s plenty of other project management tasks you can use Excel for, including creating a Kanban Board to effectively manage how the projects are run, including setting priorities for sprints, tracking progress and hours used up, all in one simple spreadsheet.Progress isn’t all you need to be able to track for a project either, and budgets can also be managed and forecasted in Excel, from the start dates to the end dates with materials tracked against the actual costs of the work being done. When projects start to overrun, you need to keep a close eye on what impact this is having on the budgets, but you don’t need to be spending extra on software to do so, because this humble spreadsheet can do it all for you.
Another potentially costly business tool is a CRM, with many solutions out there for you to try, but for smaller businesses who don’t have thousands of contacts to update on a daily basis, the question is whether you actually need to pay for one. Excel can work as a CRM for you with all of the lead information you need easily accessible and editable, particularly if saved on a tool like Sharepoint for multiple users to be able to gain access to at the same time.
Inventory & Equipment
You might not think that you need to keep track of an inventory of equipment if you’re a business that doesn’t sell or hire out physical products. However, every business has equipment, even if it is only desks, chairs and computers, and this all needs to be tracked in terms of condition and value, to help with the decisions that need to be made about maintaining and replacing them, and this can be done with a spreadsheet.You can also track and forecast the depreciation in value of your equipment or inventory over time, using the straight-line method to see the correlation of its useful life span and its salvage value and cost. Another usage is to track the inventory in a warehouse, by including and updating figures for unit prices, quantity in stock, reorder levels, reorder time in days, etc, which gives you a good overview of when new items need to be ordered.
Excel Tips & Tricks
Now that you’ve seen some of the ways that Excel can be used, here are some tips for how to get better at actually using it:- How to use AutoFill to fill adjacent cells automatically - Click and hold the lower-right point of the cell and drag it down the column or across the row and it will fill them automatically.
- How to use Conditional Formatting to highlight important values - Select the cell range you want and go to Home > Conditional Formatting. Then choose from the formats available.
- Use Flash Fill to automatically fill your data - Once you’ve put in your source data and established a pattern you want to follow, add a new column next to it. Click on the next cell below and press CTRL+E to run Flash Fill, which will identify the pattern and complete it.
- Use Forecasting - Select two corresponding sets of data, go to Data > Forecast > Forecast Sheet and choose whether you want to show it as a line chart or a column and you’ll be able to forecast your financial future.
Read next: What type of problem solver are you? (infographic)