One of Excel’s greatest tricks is the dimensional or 3D reference. This feature, which allows you to create formulas that refer to the same cell or range on multiple worksheets, simplifies the creation of complex documents like monthly inventories or sales reports. In Excel 2013, Microsoft has beefed it up with enhanced options and formulas. […]
Spreadsheet typos and oversights can wind up costing your company millions. Here’s a look at eight big mistakes, and tips on how to prevent them from happening at your company. Spreadsheet errors can happen to the best of us. As a result, many public companies and government organizations are trying to wean themselves off their […]
Spreadsheets are a stable in the business world. If you work anywhere in money, finances, business, or anything similar to those then you likely use a spreadsheet. They’re even used in the military to keep track of supplies. With so many people in so many industries using spreadsheets, you would imagine that people make a […]
Today I am going to show you how to quickly compare two tables using conditional formatting. We are comparing two price lists from the year 2013 and year 2014. To make things more interesting, price list 2014 is not sorted. new products are also introduced. It is quite common that pricelists are huge and a total […]
The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to understand and troubleshoot. Return multiple values vertically Array formula in C8: =INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, “”), […]
Problem: How to find Min and Max numeric values in a range of cells that have duplicate numbers and blanks, but only want to find the Min and Max on the largest/top 100 non-duplicate values. Solution: Array formula in cell D11: =MAX(IF((COUNTIF(Table1[Value],Table1[Value])<>1)+(Table1[Value]=””),””,Table1[Value])) Array formula in cell D12: =LARGE(IF((COUNTIF(Table1[Value],Table1[Value])<>1)+(Table1[Value]=””),””,Table1[Value]),100) Formula in cell D15: =MAX(Table1[Value]) Formula in […]
How To List Alphabets Using Fill Handle – Trick to Fill “A to Z” We can achieve this result using two methods… 1. Using FORMULA: By using a function CHAR we can achieve this result. Do you want to know how? Type =CHAR(65) in cell B2, which results “A”, capital A and proceeds 66 as […]
After Microsoft developed Excel to the 2010 & 2013 versions, 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. […]
Having a drop-down list in your Microsoft Excel spreadsheet can often improve the efficiency of your data entry, while also limiting data entry to a specific set of items or data made available in the drop-down list. Drop-down lists can greatly facilitate data entry. Here’s a look at how to use Excel’s data validation feature […]
Excel is an excellent tool for organizing, storing and manipulating data. Here are tricks that will assist with data entry into the spreadsheet: 1. Control the cell pointer during data entry: Excel automatically moves the cell pointer to the next cell down when you press Enter after entering data into a cell. This gets distracting, especially, if you […]