In our last article, we have seen the 5 biggest fails in Excel. But instead of laughing about these mistake, we should rather talk about how to find and avoid errors. In this article, we’re taking a look at 6 methods of how to prevent errors in your spreadsheet.
You’ve probably heard of the VLOOKUP formula in Excel, haven’t you? The VLOOKUP formula searches for a value in a column. Once found it returns another value from the same row. A combination of INDEX and MATCH serves the same purpose. It works slightly different and has therefore some advantages and disadvantages towards VLOOKUP.
When you copy cells or worksheets from another Excel workbook, links to other worksheets in many cases still persists. Excel offers a function to break links but this function only works with links within formulas. There are many other types of links as links within conditional formatting rules or data validation rules. The bad news: Those links can’t… Continue reading Break Links in Excel – All of Them (Even When Excel Doesn’t)
Creating Excel workbooks is often a long process: Setting up the structure, importing inputs, conducting the calculations and eventually tidying it up and sharing it. So once you are done organizing the contents, you have to make sure that the contents are delivered and received well. Therefore it’s crucial that the workbook shows a certain… Continue reading 7 Simple Tricks to Make An Excel Workbook Look Professional
You finished your calculations and now you are about to present your results? Well built Excel models usually separate the calculations from the results. Therefore “Paste as Link” might be helpful for you, especially when your colleagues should not mess your data source.
One of the most often used functions when creating an Excel model is consolidating data from different sources. There are 3 major formulas for combining data from different tables or worksheets: VLOOKUP, SUMIFS and INDEX/MATCH. VLOOKUP and SUMIFS are rather popular whereas INDEX/MATCH is usually not that well known. So what is the difference between these… Continue reading When to use VLOOKUP, SUMIFS or INDEX/MATCH in Excel
Let’s assume, we have the following (although realistic) challenge: We got two lists which should have the same items in Excel. But they aren’t exactly the same so that we need to compare them. But how do we find out the best way, which items are missing in either one of the lists? Instead of comparing… Continue reading How to Compare Two Lists in Excel
Unhiding hidden worksheets in Excel was for a long time troublesome, especially if there were many hidden worksheets in your workbook. Fortunately, Microsoft has just released a new feature in Office 365 (now: “Microsoft 365) for unhiding multiple sheets at once. So far, so good. But what, if you want to unhide “very hidden” sheets?… Continue reading How to Unhide All Hidden & ‘Very Hidden’ Excel Sheets at Once