How to display fractions in Excel (the easy way)

When using Excel, we often display numbers as decimals. However, that is not necessarily the best way for users to read the information; fractions can be a better option. We could enter fractions as text, but then we can’t use them in calculations. So, in this post, we’ll look at how to display fractions in Excel. About Read More

Excel Table absolute reference for cells, columns, and rows

One of the first things we learn in Excel is the magic of the $ symbol. It freezes the row or column, so the cell reference does not change when copying a formula. This is known as an absolute reference. With the introduction of Tables came a different (and more semantic) way to reference cells, called structured Read More

How to convert color codes (RGB, HSL, HSV, Hex, Long, CMYK)

I often find myself converting between different formats of color codes; especially from hex to RGB and back again. Rather than firing up Google and searching for color conversion tools online, I decided to write my own User Defined Functions in Excel, now I can calculate the result within a worksheet instead. The functions in this Read More

How to use Excel Table within a data validation list (3 ways)

Excel Tables expand automatically whenever new data is added to the bottom. This feature alone makes Tables one of the most powerful tools within the Excel users toolkit. A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature. Data validation lists would Read More

VBA protect and unprotect Sheets (25+ examples)

Protecting and unprotecting sheets is a common action for an Excel user. There is nothing worse than when somebody, who doesn’t know what they’re doing, overtypes essential formulas and cell values. It’s even worse when that person happens to be us; all it takes is one accidental keypress, and suddenly the entire worksheet is filled with Read More