Traps of Custom Formatting

Numbers can coexist in Excel cells with text strings. What may surprise you is that the contents of such cells can be treated and used as numbers. It means that they can be used as ordinary numbers in mathematical calculations, functions and formulas. This happens when you apply some kinds of custom formatting for cells.

Here is such example of cell formatting and some confusing results of using it in formulas involving text strings.

I've used the following custom format for cells in column A:

Template for Basic Data Summary/Analysis

If you have collected and organized some data in table(s) or list(s), the first thing you might need could be to get quick general analysis/evaluation of that data - before going any further with some math, statistical or graphic analysis.

The following example provides easy solution, kind of a template, for that preliminary summary and basic analysis.

Here's just illustrative table of random data. Let's say you want to know "everything" about this set of values.

COLORS in Cells: How to Get, Set and Use them

It's really easy to color Excel cells, their background, content or borders. We can do it either directly (using Format Cells... or some Font options) or indirectly - using Conditional Formatting in a variety of available ways in the Excel menu.

When it comes to determining (getting) exact codes/names of colors being already used in a worksheet, it can be a bit complicated. And it may happen that you need to know what colors were originally used for background filling of some cells. Well, we can't find it out by using any Excel function or formula. So, practically, we must use macros (VBA codes) to solve such puzzles. E.g., the following code will determine Color Index for background color of cell A2.

Press ALT+F11 keys to display the VBA editing window and enter there this code:

Sub getCellBG()

Dim getC as Integer

getC=Range("A2").Interior.ColorIndex

End Sub

TIPS for CLICKS: Working with Functions and Formulas

Excel FUNCTIONS = Predefined formulas used for specific values in a specific order. They operate usually on data entered in range of cells and increase the Excel users productivity. Total number of the functions available in Excel is close to 700.

Some of the most important of them (most frequently used) are: SUM, IF, MIN, MAX, AND, OR, AVERAGE, COUNT, DAYS, VLOOKUP, DATE, ... etc. Examples of functions working with text: UPPER, LOWER, PROPER,... etc.

Excel FORMULAS = Expressions operating on values in a range of cells and operators. Can utilize several Excel functions and work with both numeric and textual data. ARRAY formulas are a special type of formulas, and can be very complex.

Here is the list and description of some of the most useful shortcuts used in conjunction with Functions and Formulas. I hope you'll find them helpful.

How to highlight space between two plotted curves / lines

If you want to enhance your Excel charts, to fill with color some target range of the data, this procedure may be helpful.

I'm providing here two examples of such enhanced charts. In addition, I've included procedure for calculation of the surface area bounded by the two curves/lines.

Chart #1