5 Ways to Supercharge Spreadsheets
Most businesses use Excel in the course of their day-to-day work; many companies would even say that productivity would drastically decrease if they were unable to use it. Most people have a good understanding of how to use Excel, but it is a powerful tool with much more depth than people know. Here are 5 lesser-known Excel tips and tricks to help supercharge your business:
1. Absolute vs Relative Cell References
One well known feature that Excel has is the ability to drag formulas and have them update the cells used within it. However, this can sometimes cause the formula to incorrectly change a cell used in the formula. Absolute cell references can prevent the formula from changing certain cells. To make a cell absolute a dollar sign needs to be typed before the letter ($A) to prevent the column from changing and before the number ($1) to prevent the row from changing. Typing a dollar sign in front of both the letter and number ($A$1) prevents the row and column from changing as the formula is auto filled.
2. Sparklines
Charts are often used to help people visualise data. When presented with lots of data having lots of charts can detract from the visualisation – sparklines can provide a solution here. Sparklines put the visualisation in a single cell next to the data, allowing data to be displayed alongside its corresponding chart. This is beneficial for presentations or reports.
3. Named Ranges
Spreadsheets can contain vast amounts of data and formulae, and it can be quite the challenge knowing the location of what you need. Named ranges can help make detailed spreadsheets much more readable and maintainable. By dragging and selecting the range of data it can be renamed using the textbox. This named range can now be referenced within formulae (A demonstration of which can be seen in the image below).
4. Freezing Columns and Rows
When a large dataset is being used, it can often be tedious having to scroll back to the top to see what column is being looked at. By freezing rows and/or columns, the headings can travel with you down the page, saving time and effort.
5. Macros
Macros are one of the most powerful and misunderstood aspects of Excel. They allow you to automate a series of repeated actions, binding them to a single click – reducing time and effort. Advanced macros can be written manually in code, but macros can also be created by recording. Recording macros can be done by navigating to the developer tab of the spreadsheet, press record and carry out the actions that should be saved, and the spreadsheet remembers your steps. Next time this process needs to be done instead of manually carrying it out by using the macro it will carry out all the previously recorded steps.
Excel has many more tools but mastering these five skills will improve efficiency and increase productivity.
Find Out More
If you enjoyed this briefing paper, check out our other digital resources which cover a wide range of topics, including quantum computing, social media, and 3D printing.
The Lancashire Cyber Foundry runs a series of business strategy and cyber workshops specifically designed for SMEs in Lancashire. We’re passionate about seeing Lancashire business become more cyber-aware and innovative and so offer funded places for companies to come and learn how to defend, innovate and grow their business. Additionally, we have an experienced technical team ready to help you with your business innovation ideas, particularly around cyber and digital innovation.
To find out more about how your business can access support or register on one of upcoming workshops, contact us:
About the Author
James Humphreys
James Humphreys is a 22-year-old MSci Computer Science student with a keen interest in cyber-security and secure computing. As part of his master’s degree he worked with the LCF as a student intern for a client where he aided them in creating more efficient processes by improving their spreadsheets. Outside of work he is a keen lacrosse player.
Interested? Email us
Sign up to our newsletter
Get the latest updates on news and events from the Lancashire Cyber Foundry team.
By filling in this form you register for our e-newsletter, which will help explain the programme and how we could benefit your business. Registering does not place you under any obligation, and you can unsubscribe from communications at any time using the unsubscribe link at the bottom of our newsletters. Lancaster University will hold and use the information which you supply in line with our privacy policy.
