Cell references in copied formulas are automatically amended by Excel, e.g. to refer to cells in the same relative row or column. Often this makes sense, e.g. you want a SUM formula to always add the values in the same column as the SUM formula. But sometimes this leads to errors! In this blog, you will learn how to avoid such errors.
You have customised a chart, then copied both chart and related data to another area ready for new data. You then amend the copied data, e.g. to show sales of a different business and…. oh! There is a problem…
Did you know that for large, slow workbooks you can switch the calculations to manual and only recalculate when necessary? This can speed up working with your file dramatically. To recalculate, there are useful shortcuts you should know.
Nearly 90% of spreadsheets contain errors and many are also hard to use! These can be expensive in terms of money, time and reputation. So how can you avoid these issues? For my new book I went back to basics and completely overhauled my best practice advice. The result is my new, improved Gary’s Golden Ground Rules.
COUNT = Calculate Once, Use Numerous Times. If you need to use a value multiple times, you should calculate it just the once and then link back to that value in all subsequent calculations. This can make your calculations shorter, easier to understand and faster.
Pit stop times have been dramatically reduced over the years by following a set of six principles. I have adapted these to Excel and explain them in this blog. If you follow them, I estimate that you can easily save 10% of your time working in Excel. If you worked full-time in Excel, you would save four hours a week or 22 days a year!
You can perform many tasks faster if you set up and use the Quick Access Toolbar. In this blog you will learn how to set up your Quick Access Toolbar so you can speed up your work!
You can perform many tasks faster if you use keyboard shortcuts. Learn the best ones for common tasks here and speed up your work!
Copy and paste are two of the most frequently performed actions in Excel, but when you paste, you don't necessarily want to paste everything. Sometimes you need a paste special! It is easy to do and there are loads of options so you can easily save time and also avoid pasting unnecessary content which you have to tidy up afterwards.
Did you know you can easily paste copied data as values? In this blog I explain why that can be very important and how to do it quickly with keyboard or mouse (spoiler: it's dead easy!)
Do you need to check your loan repayments, depict a loan in a financial model or calculate your IFRS16 balances? Learn how to develop flexible amortisation schedules for loans and financial leases with simple inputs and automated calculations in Excel. You can also download a file with four versions for free under Downloads!
Psychologist Abraham Maslow developed his well-known pyramid to better understand human needs and motivation, the idea being that people strive to reach higher levels. I was recently inspired to amend this and apply it to Excel. What do the motivation levels of the Excel pyramid mean for you and how can you climb them? In this blog I will answer both questions and give you links to useful resources.
Filters are great for, well, filtering data so you only see rows of data which you are interested in, e.g., invoices for a certain customer. Here I show you three quick tips to help you in your daily work.
It's Christmas time again, the season of cheer. You can bring a little festive merriness to your PC by simulating Christmas tree lights using the magic of random numbers combined with conditional formatting. Why not have a go yourself? You can also download the file with instructions under downloads.
Some tasks can only (efficiently) be performed by one or the other. But you may be surprised at how many tasks can be completed be either and you may be further surprised how much more efficiently you can work with one rather than the other. So, which one is best to use?
SUMIFS is a great function but (i) if data is in other files it can generate errors if the other file is closed and (ii) it only works in one dimension (rows or columns, not both). In this blog I show you how to solve both problems and how to use conditional formatting to highlight the summed cells.
Get where you want to, quickly and easily. You can work more efficiently in Excel if you can navigate effectively. With regular practice of the methods I explain, you too can become an expert in Excel navigation!
Excel offers a huge variety of charts (also commonly called graphs) so it can be difficult to identify the most suitable type for presenting your data. Here I explain some basic guidelines which should help you pick the best type in several typical cases and some useful tips. I also give some advice on which chart types are not recommended.