Categories
Excel

Using Code and Char Functions

The CHAR and CODE functions in Excel are useful for working with characters and their numeric codes. The CHAR function returns the character corresponding to a given numeric code, while the CODE function returns the numeric code of a given character.

Categories
Excel

Creating Dependent Combo Box Using Option Buttons

Accessing three lists of choices simultaneously generally requires that you use three separate controls, such as three ComboBox control. Instead, you can use a ComboBox in combination with option buttons (also called radio buttons) to have a list change automatically according to which option button you choose.

Categories
Excel

Turn Data Validation On and Off with a Checkbox

One way to prevent users from entering wrong data is to use data validation. Data validation limits the type of data that can be entered in the cells. Sometimes, you might want to allow users to enter data that is not allowed by the data validation rules. Instead of removing the data validation, you can use a simple checkbox to override it.

Categories
Excel

Enable/Disable Conditional Formatting with a Checkbox

Conditional formatting is a powerful feature of Excel that lets you change the appearance of cells based on their values. However, using the menus and dialog boxes of the GUI to apply and remove conditional formatting can be tedious and time-consuming. A better way to control conditional formatting is to use checkboxes on your worksheet that let you switch it on and off easily and quickly. This way, you can view your data in different ways whenever you want, without going through multiple steps.

Categories
Excel

Create 3D Effects in Tables or Cells

Whenever you see a nifty 3D effect in a program or application such as Excel, you are actually seeing an illusion created by specific formatting. It is easy to create this illusion yourself by applying formatting to a cell or range of cells.

Categories
Excel

MOD Function: Highlight Every Other Row or Column

You can apply different colors to rows or columns based on their position using conditional formatting and the MOD function. This function returns the remainder of a division operation. For instance, you can use it to check if a row number is odd or even and assign a color accordingly.

Categories
Excel

Sum Cells That Meet Conditional Formatting Criteria

Conditional formatting is a useful feature in Excel that allows you to highlight data based on certain criteria. But what if you want to use formulas that only refer to the cells that have conditional formatting applied? Excel doesn’t have a built-in function for this, but there are some workarounds that you can use.

Categories
Excel

Highlighting Formula Cells with Conditional Formatting

The IsFormula returns TRUE if the reference cell houses a formula and FALSE if it does not. You can use this Boolean result in conjunction with conditional formatting so that all formulas are highlighted automatically in a format of your choice.

Categories
Excel

Setting Up Checkboxes for Conditional Formatting

Conditional formatting in Excel allows you to format selected cells based on values or formulas you specify. However, enabling and disabling it through the ribbon and dialog boxes can be a hassle. Fortunately, by incorporating checkboxes into your worksheet, you can effortlessly control the formatting and enhance your ability to interpret data according to your preferences.

Categories
Excel

Dynamic Data Validation Lists with Spill Ranges

The UNIQUE and FILTER functions are very useful for providing a dynamic list of items for a Data Validation list. In this tutorial, we will use the two columns of a table to generate a dependent drop-down list with spill ranges.