Sep 30, 2020 26 Excel Tips for Becoming a Spreadsheet Pro Microsoft's program does just about everything one could ask for in a spreadsheet. Become the office Excel guru in no time with these hacks and tricks. Everything's just simpler with a Table. To learn more about Excel Table objects. Excel errors: How Microsoft's spreadsheet may be hazardous to your health (ZDNet) Editor's Picks.
What Excel Is Used For. Electronic spreadsheet programs were originally based on paper spreadsheets used for accounting. As such, the basic layout of computerized spreadsheets is the same as the paper ones. Related data is stored in tables — which are a collection of small rectangular boxes or cells organized into rows and columns. After Microsoft developed Excel to the 2010 version, it offered more surprises than ever. In order to deal with tons of big data, you can't ignore the important role Excel plays in daily work. However, both for beginners and advanced users, there are still many useful tips and tricks that are inevitably overlooked.
Excel advanced training
In order to create and maintain with consistently good Excel files, there are a number of best practices to keep in mind. Here we provide a list of Excel tips to improve the way you work with spreadsheets.
Want to become great at Excel?
Introduction to creating and maintaining good spreadsheets
Because of its flexibility, Excel is considered an easy general-purpose tool for spreadsheets. However, being easy to use comes with its own drawback. It is very easy to make mistakes in complex spreadsheets. Errors that are commonly found are incomprehensible calculations, complex relations, hidden information or just plain mistakes.
We want to help you create and maintain good spreadsheets. That's why we've set up a list of expert Excel best practices, that address commonly made mistakes when using Excel. Use these tips as a tool to create clear and concise spreadsheets, right from the start.
1. Preparation of a good Spreadsheet
The first of our absolute Excel best practices is to choose an organization standard before developing your spreadsheet. Stick with it for as long as you're using the spreadsheet. An organization standard sets the stage for all future users who end up working with the spreadsheet. A shared standard improves communication and saves up in development time. Standardized organization may include cell formatting, general layout, color scheme, ordering, etc.
2. Create Worksheets with the Future in Mind
It is important to be prepared. You might come across situations in the future that weren't present when you started setting up your spreadsheet. For instance, there may be additions that come into play later. Or perhaps you need room for values that depend on events that are bound to change over time. Try to consider as much future factors as possible that might possibly force you to change a worksheet. By preparing for any future developments, you increase the lifespan of the worksheet. A longer lifespan means you waste less time on creating a replacement worksheet. A good preparation is therefore one of the most important Excel tips we can give you.
3. Think about the Order of Worksheets
Put different kinds of data on different worksheets. For example, use the first few worksheets for input information, the following worksheets for calculations and the last sheet as a presentation worksheet for graphs and results. Limit the amount of tables per worksheet to just one. Multiple tables per worksheet cause problems when attempting to sort, insert or format cells.
Explanatory worksheets show other users how the spreadsheet should be used.
When creating a large number of worksheets, add an explanatory worksheet or a worksheet dedicated to a table of contents. This makes it easier for someone to understand how the spreadsheet is set up. Explanatory worksheets also show other users how the spreadsheet should be used.
4. Choose Clarity over Looks
If your worksheet is user oriented, use an attractive 'Results Worksheet'. If not, don't! Most worksheets work best when they are designed to provide clarity of all present calculations. You can try splitting up long formulas, but do not hide them to provide more clarity for users.
5. Keep your Timeline Consistent
Keep the timeline consistent across all worksheets, even if this leads to empty rows. Consistent timelines vastly improve the clarity of the spreadsheet and reduce the risk of incorrect formulas. For example, shortening four months into quarterly figures might give a more clear design in terms of presentation, but it also raises the risk of incorrect totals. An easy way to avoid this is to insert a single timeline per worksheet, ideally at the top, in a frozen header row.
6. Organize the Information Flow
Try to organize worksheets in such a way that information always flows from top left to bottom right. This makes it considerably easier for a user to understand how the sheet works. Exceptions to the rule are an 'Input Worksheet' and/or a 'Results Worksheet'. Putting these at the start increases clarity for users, which is especially useful for a user oriented worksheet. When managing the flow of information, avoid criss-cross dependencies as they greatly detract from understandability. At all times avoid circle chain relations, if a link to any previous data is needed.
7. Label Columns and Rows
Columns without clear and consistent names might not be problematic for the creator of the worksheet, but to other users it might appear confusing. That is why our next Excel tip is to always label columns of tables with simple names that consistently follow previously used naming conventions. The same applies to horizontal tables. In these cases, only use row labels.
8. Keep Formulas Readable
The 8th best practice in Excel is to split up calculations into multiple smaller calculations. This is a great way to increase readability, just like the correct use of spacing. Too many different operators or too many different references in a formula can make the formula unreadable. This causes the spreadsheet to be hard to use by anyone other than its creator. The original author ends up always being the one having to explain it to others, or even ends up being the only user actually able to work with the spreadsheet.
Many spreadsheets are hard to use by anyone other than its creator.
9. Avoid Repetitive Formulas
Avoid repetitive calculations. An exact duplicate of a formula doubles the risk for errors. In addition, changes in one formula are not automatically replicated in duplicates. Sometimes, these duplicates are overlooked, causing inconsistencies within the spreadsheet. When the use of the exact same formula is required, refer back to the first instance of the formula. In extreme situations it may be necessary to break a formula down to maintain clarity.
10. Avoid Fixed Numbers in Formulas
Use a separate input cell for fixed values and employ references to this cell in order to use it in calculations. A formula that contains fixed numbers is a major risk. If the value ever changes, it needs to be changed in every instance it appears. This is, at the very least, cumbersome when dealing with large worksheets. At worst, it is one of the easiest ways to create mistakes.
11. Do not Merge Cells
Merging cells is seldom good practice. It is usually done to improve aesthetics, but it eventually leads to an increased risk of problems with calculations and references. The biggest risk is making references to merged cells. In a referenced merged cell, all cells can be part of the calculations, but only one of those cells is going to be the correct part. This ambiguity gives way to an increasing amount of errors the longer the spreadsheet is being used.
12. Avoid Hiding Data
Hiding columns, rows, or even entire worksheets from view is almost never a good idea. It only increases the chance that a user overlooks something important when working with the spreadsheet, increasing the risk for errors. The only exception to this rule would be when it is absolutely necessary to hide information that somehow can not be put into a separate worksheet. Since it's almost always possible to separate sensitive data, these cases are rare.
Excel Spreadsheet Free Download
Hiding data increases the risk for errors.
13. Build in Data Verification
Building in data verification, such as audit tests, alerts and automated checks, is a good way to avoid making any damaging changes or additions to existing work. It is important to build these in when the worksheet is created. Not doing so often leads to forgetting about them later on. Data verification is an excellent way to build a strong foundation for worksheet maintainability.
14. Save Styling for the End
Excel's formatting options are quite extensive. They allow for the fine-tuning of cell appearance, values, and plenty of options for colors, borders, and features alike. While styling can help keep a spreadsheet understandable, the process of doing so includes abstracting information for the viewer. This is good for the final product, but makes development burdensome and increases the risk of errors. The best way to go is saving the style of a worksheet for last.
15. Keep Styling Consistent
Your choice of styling should remain consistent throughout the entire spreadsheet. A simple and consistent style for formatting is critical to help viewers understand your spreadsheet. Always include a legend. Abbreviations and colored cell definitions can be listed on a separate worksheet if needed.
16. Keep Conditional Formatting Simple
Conditional formatting helps the viewer understand how the spreadsheet works. Complex formatting rules defeat that purpose by obscuring how the spreadsheet is set up. It also causes confusion for the user. Keep the rules for conditional formatting simple (e.g. changing colors based on cell content).
17. Use Positive Numbers
Ever accidentally subtracted what should have been added? Chances are this was the result of an input cell that was entered as a negative number. Always try to build a spreadsheet that promotes the use of positive numbers.
18. Be Clear Which Units Are Used
Make sure viewers can always trace the units that are used in the spreadsheet. You can do this by either setting the units in the cell properties within Excel, or you can mention them in a column label. Messenger plus telegram. But beware: never type a currency symbol (€, $, £ etc.) directly after a value. This causes problems when using the value in a formulas.
19. Clarify your Sources
When complicated calculations in Worksheet A are made using cells from Worksheet B, show the viewer those cells from Worksheet B in Worksheet A in your spreadsheet. Users that trying to understand how the calculation works can stay on the same worksheet, instead of having to switch worksheets. Make your references clear and understandable. Without proper formatting, references can cause a worksheet to look cluttered, especially if there are a lot of them.
20. Avoid the Use of Macros
Always use Excel's solutions before resorting to VBA macros. VBA macros make the spreadsheet less transparent, as they abstract away logic. For some tasks, VBA macros even perform worse than Excel's default solutions.
21. Use Simple Ranges
For many calculations in your spreadsheet, it is good practice to incorporate additional empty rows or columns. This prevents future edits of the spreadsheet from messing up your formulas. The less you use separate small ranges, the smaller the chance of erroneous calculations in the future.
How can PerfectXL help you?
Share your questions about our products. We will be
in touch with you in the next 48 hours.
Explore the 5 must-learn 'fundamentals' of Excel
Getting started with Excel is easy. Sign up for our 5-day mini-course to receive easy-to-follow lessons on using basic spreadsheets.
Free Excel Spreadsheet
- The basics of rows, columns, and cells..
- How to sort and filter data like a pro..
- Plus, we'll reveal why formulas and cell references are so important and how to use them..
Microsoft Excel is a software program included in the Microsoft Office suite. It is used to create spreadsheets, which are documents in which data is laid out in rows and columns — like a big table.
Due to its extreme versatility and power, Excel has become one of the most-used software programs in the business world since its launch in 1985. Indeed, the personal computing renaissance of the 1980s and 1990s was largely driven by the many uses of Excel and other spreadsheet software.
What is a spreadsheet?
A spreadsheet is a special way of organizing data into rows and columns to make it simpler to read and manipulate. Here's a screenshot of a blank spreadsheet:
As you can see, the document is comprised of columns (the vertical sets of boxes labeled A, B, C, etc. on the top of the screen), and rows (the horizontal sets of boxes labeled 1, 2, 3, etc. on the bottom of the screen). At the intersection of each row and column is a cell into which a user can enter either numbers or text. The address of any given cell is generated by combining the letter of the cell's column with the number of the cell's row. For example, the cell highlighted in the diagram below is at address B4, because it lies at the intersection of column B and row 4. When we refer to this cell verbally, we call it, 'Cell B4.'
The image above, of course, represents a very simple spreadsheet. More complex spreadsheets can contains hundreds or even thousands of rows and columns, the combination of which can represent millions of cells. These cells can be linked together with calculations and formulas to perform complex mathematical logic simply and easily.
What is Excel used for?
That's all well and good, but what are spreadsheets used for? Almost everything. Spreadsheets are extraordinarily powerful tools, and are used frequently in the business world to store and manipulate data.
Throughout the course of these tutorials, we'll be following SnackWorld, a fictional company, as it uses Excel to track data about its business. Here's one example of how SnackWorld uses Excel in the workplace:
As you can see, SnackWorld has used the above Excel spreadsheet to keep track of its sales numbers by item sold. The tabular format of a spreadsheet is the perfect way to measure this complex set of data — it would take a long time for SnackWorld's managers to write this out by hand! And Excel's formulas and functions, which we'll learn later on in this module, allow employees to easily pull out specific pieces of data to answer managers' questions.
Here are a few examples of other ways a company might use Excel. Remember, though, that these are just examples. Excel is infinitely flexible, so the possibilities are endless:
- Keep track of sales by customer from month to month;
- Monitor customer payments to ensure that none are late;
- Keep track of expenses and assign them to particular employees;
- Calculate hours worked per employee for monthly payroll;
- Calculating monthly payments on the office mortgage;
- Creating graphs or charts to explain company performance over time;
- Estimate monthly sales for the next three years based on historical data; and
- Assign projects and tasks to employees in a centralized location.
How to learn Excel
Now that you know what Excel is, it's time to learn how to use it! Get started with our how to use Excel article and begin to harness the power of this amazing tool.
Explore the 5 must-learn 'fundamentals' of Excel
Getting started with Excel is easy. Sign up for our 5-day mini-course to receive easy-to-follow lessons on using basic spreadsheets.
Keep the timeline consistent across all worksheets, even if this leads to empty rows. Consistent timelines vastly improve the clarity of the spreadsheet and reduce the risk of incorrect formulas. For example, shortening four months into quarterly figures might give a more clear design in terms of presentation, but it also raises the risk of incorrect totals. An easy way to avoid this is to insert a single timeline per worksheet, ideally at the top, in a frozen header row.
6. Organize the Information Flow
Try to organize worksheets in such a way that information always flows from top left to bottom right. This makes it considerably easier for a user to understand how the sheet works. Exceptions to the rule are an 'Input Worksheet' and/or a 'Results Worksheet'. Putting these at the start increases clarity for users, which is especially useful for a user oriented worksheet. When managing the flow of information, avoid criss-cross dependencies as they greatly detract from understandability. At all times avoid circle chain relations, if a link to any previous data is needed.
7. Label Columns and Rows
Columns without clear and consistent names might not be problematic for the creator of the worksheet, but to other users it might appear confusing. That is why our next Excel tip is to always label columns of tables with simple names that consistently follow previously used naming conventions. The same applies to horizontal tables. In these cases, only use row labels.
8. Keep Formulas Readable
The 8th best practice in Excel is to split up calculations into multiple smaller calculations. This is a great way to increase readability, just like the correct use of spacing. Too many different operators or too many different references in a formula can make the formula unreadable. This causes the spreadsheet to be hard to use by anyone other than its creator. The original author ends up always being the one having to explain it to others, or even ends up being the only user actually able to work with the spreadsheet.
Many spreadsheets are hard to use by anyone other than its creator.
9. Avoid Repetitive Formulas
Avoid repetitive calculations. An exact duplicate of a formula doubles the risk for errors. In addition, changes in one formula are not automatically replicated in duplicates. Sometimes, these duplicates are overlooked, causing inconsistencies within the spreadsheet. When the use of the exact same formula is required, refer back to the first instance of the formula. In extreme situations it may be necessary to break a formula down to maintain clarity.
10. Avoid Fixed Numbers in Formulas
Use a separate input cell for fixed values and employ references to this cell in order to use it in calculations. A formula that contains fixed numbers is a major risk. If the value ever changes, it needs to be changed in every instance it appears. This is, at the very least, cumbersome when dealing with large worksheets. At worst, it is one of the easiest ways to create mistakes.
11. Do not Merge Cells
Merging cells is seldom good practice. It is usually done to improve aesthetics, but it eventually leads to an increased risk of problems with calculations and references. The biggest risk is making references to merged cells. In a referenced merged cell, all cells can be part of the calculations, but only one of those cells is going to be the correct part. This ambiguity gives way to an increasing amount of errors the longer the spreadsheet is being used.
12. Avoid Hiding Data
Hiding columns, rows, or even entire worksheets from view is almost never a good idea. It only increases the chance that a user overlooks something important when working with the spreadsheet, increasing the risk for errors. The only exception to this rule would be when it is absolutely necessary to hide information that somehow can not be put into a separate worksheet. Since it's almost always possible to separate sensitive data, these cases are rare.
Excel Spreadsheet Free Download
Hiding data increases the risk for errors.
13. Build in Data Verification
Building in data verification, such as audit tests, alerts and automated checks, is a good way to avoid making any damaging changes or additions to existing work. It is important to build these in when the worksheet is created. Not doing so often leads to forgetting about them later on. Data verification is an excellent way to build a strong foundation for worksheet maintainability.
14. Save Styling for the End
Excel's formatting options are quite extensive. They allow for the fine-tuning of cell appearance, values, and plenty of options for colors, borders, and features alike. While styling can help keep a spreadsheet understandable, the process of doing so includes abstracting information for the viewer. This is good for the final product, but makes development burdensome and increases the risk of errors. The best way to go is saving the style of a worksheet for last.
15. Keep Styling Consistent
Your choice of styling should remain consistent throughout the entire spreadsheet. A simple and consistent style for formatting is critical to help viewers understand your spreadsheet. Always include a legend. Abbreviations and colored cell definitions can be listed on a separate worksheet if needed.
16. Keep Conditional Formatting Simple
Conditional formatting helps the viewer understand how the spreadsheet works. Complex formatting rules defeat that purpose by obscuring how the spreadsheet is set up. It also causes confusion for the user. Keep the rules for conditional formatting simple (e.g. changing colors based on cell content).
17. Use Positive Numbers
Ever accidentally subtracted what should have been added? Chances are this was the result of an input cell that was entered as a negative number. Always try to build a spreadsheet that promotes the use of positive numbers.
18. Be Clear Which Units Are Used
Make sure viewers can always trace the units that are used in the spreadsheet. You can do this by either setting the units in the cell properties within Excel, or you can mention them in a column label. Messenger plus telegram. But beware: never type a currency symbol (€, $, £ etc.) directly after a value. This causes problems when using the value in a formulas.
19. Clarify your Sources
When complicated calculations in Worksheet A are made using cells from Worksheet B, show the viewer those cells from Worksheet B in Worksheet A in your spreadsheet. Users that trying to understand how the calculation works can stay on the same worksheet, instead of having to switch worksheets. Make your references clear and understandable. Without proper formatting, references can cause a worksheet to look cluttered, especially if there are a lot of them.
20. Avoid the Use of Macros
Always use Excel's solutions before resorting to VBA macros. VBA macros make the spreadsheet less transparent, as they abstract away logic. For some tasks, VBA macros even perform worse than Excel's default solutions.
21. Use Simple Ranges
For many calculations in your spreadsheet, it is good practice to incorporate additional empty rows or columns. This prevents future edits of the spreadsheet from messing up your formulas. The less you use separate small ranges, the smaller the chance of erroneous calculations in the future.
How can PerfectXL help you?
Share your questions about our products. We will be
in touch with you in the next 48 hours.
Explore the 5 must-learn 'fundamentals' of Excel
Getting started with Excel is easy. Sign up for our 5-day mini-course to receive easy-to-follow lessons on using basic spreadsheets.
Free Excel Spreadsheet
- The basics of rows, columns, and cells..
- How to sort and filter data like a pro..
- Plus, we'll reveal why formulas and cell references are so important and how to use them..
Microsoft Excel is a software program included in the Microsoft Office suite. It is used to create spreadsheets, which are documents in which data is laid out in rows and columns — like a big table.
Due to its extreme versatility and power, Excel has become one of the most-used software programs in the business world since its launch in 1985. Indeed, the personal computing renaissance of the 1980s and 1990s was largely driven by the many uses of Excel and other spreadsheet software.
What is a spreadsheet?
A spreadsheet is a special way of organizing data into rows and columns to make it simpler to read and manipulate. Here's a screenshot of a blank spreadsheet:
As you can see, the document is comprised of columns (the vertical sets of boxes labeled A, B, C, etc. on the top of the screen), and rows (the horizontal sets of boxes labeled 1, 2, 3, etc. on the bottom of the screen). At the intersection of each row and column is a cell into which a user can enter either numbers or text. The address of any given cell is generated by combining the letter of the cell's column with the number of the cell's row. For example, the cell highlighted in the diagram below is at address B4, because it lies at the intersection of column B and row 4. When we refer to this cell verbally, we call it, 'Cell B4.'
The image above, of course, represents a very simple spreadsheet. More complex spreadsheets can contains hundreds or even thousands of rows and columns, the combination of which can represent millions of cells. These cells can be linked together with calculations and formulas to perform complex mathematical logic simply and easily.
What is Excel used for?
That's all well and good, but what are spreadsheets used for? Almost everything. Spreadsheets are extraordinarily powerful tools, and are used frequently in the business world to store and manipulate data.
Throughout the course of these tutorials, we'll be following SnackWorld, a fictional company, as it uses Excel to track data about its business. Here's one example of how SnackWorld uses Excel in the workplace:
As you can see, SnackWorld has used the above Excel spreadsheet to keep track of its sales numbers by item sold. The tabular format of a spreadsheet is the perfect way to measure this complex set of data — it would take a long time for SnackWorld's managers to write this out by hand! And Excel's formulas and functions, which we'll learn later on in this module, allow employees to easily pull out specific pieces of data to answer managers' questions.
Here are a few examples of other ways a company might use Excel. Remember, though, that these are just examples. Excel is infinitely flexible, so the possibilities are endless:
- Keep track of sales by customer from month to month;
- Monitor customer payments to ensure that none are late;
- Keep track of expenses and assign them to particular employees;
- Calculate hours worked per employee for monthly payroll;
- Calculating monthly payments on the office mortgage;
- Creating graphs or charts to explain company performance over time;
- Estimate monthly sales for the next three years based on historical data; and
- Assign projects and tasks to employees in a centralized location.
How to learn Excel
Now that you know what Excel is, it's time to learn how to use it! Get started with our how to use Excel article and begin to harness the power of this amazing tool.
Explore the 5 must-learn 'fundamentals' of Excel
Getting started with Excel is easy. Sign up for our 5-day mini-course to receive easy-to-follow lessons on using basic spreadsheets.
- The basics of rows, columns, and cells..
- How to sort and filter data like a pro..
- Plus, we'll reveal why formulas and cell references are so important and how to use them..
Comments
Everything About Excel Spreadsheet Template
Please enable JavaScript to view comments.