#Excel Short and Sweet Tip #18 (Toggle Formatting on Worksheet) by David Hager

You might at some point want to view a section of a workbook without any user-applied formatting. It turns out that this is easy to do through conditional formatting. For this example, A4:G10 has data that has a simple table format applied, as shown below.


There is a Data Validation list option in cell B2, with the list in A1:A2 (On, Off). Then, a conditional format is applied to A4:G10 with the formula =IF($B$2=”Off”,1,0).


The important part of this process is selecting formatting options that look “normal”, such as no fill, no borders and a black font. When the Off value is selected, the formatting of the table “disappears”, as shown below.


You can download the example file here.


#Excel Super Links #46 – shared by David Hager

Excel Google Translate Function


5 Rules for a Dashboard Color Palette


100+ VBA Interview Questions and Answers with Examples


Generate QR code with google chart API using UDF in Excel


#Excel UDF Using Google API to Return the Elevation of an Address by David Hager


#Excel Super Links #45 – shared by David Hager

How To Transform Data by Example in Excel


RegEx UDF in Excel


Excel VBA get data from PDF file into Sheet(s) or Text file(s)


Excel VBA Trivial Pursuit


#Excel Short and Sweet Tip #15 (Using TEXTJOIN With Non-Contiguous Ranges) by David Hager



#Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) by David Hager

Most of the published methods that convert a numeric grade to a letter grade either use a lookup table or do not account for + or – scores. The following formula overcomes both of those limitations. For the following formula, the numeric grade is in cell A5.



#Excel: Project Tracking Workbook by David Hager

This workbook is one of many Excel projects I have worked on over the years. I am sharing it here for the first time. I am not going to discuss how this application works but rather offer it up to the user as both a useful tool and an opportunity to explore the formulas used in its construction. It is important to note that the workbook is completely unprotected and contains no VBA code.
The ProjSetup worksheet is for data input. Column A is for the names of the projects to track. Column B is for the starting date of the project and Column C is for the finish date of the project. Column D is the priority of the project – low, medium or high. A Data Validation list allows for those values to be selected, and conditional formatting determines the color. Column F has an option box to select between days and weeks. The following figure shows these features.


The ProjTimeDisplay worksheet shows the project time schedules. This was constructed with Excel formulas and conditional formatting. Feel free to dig in to see how it works. Here is a figure to visualize the output.


The project workbook can be downloaded here.


#Excel Super Links #44 – shared by David Hager

Excel Formulas Can Make a Graph http://www.excelhero.com/blog/2012/05/excel-formulas-can-make-a-graph.html

2D non-linear FEA with Excel


Generating Permutations in Excel


Excel Magic Trick 1427: DAX GEOMEANX function: Average Compounding Rate per Period for FV Calc


Copy #Excel Chart as a Enhanced Metafile Picture by David Hager


#Excel Short & Sweet Tip #16 (Multiple Delimiters with TEXTJOIN for Custom Formatting) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,


it shows an example at the bottom of the article where the 1st argument of the TEXTJOIN function uses multiple delimiters. I knew that multiple delimiters could be used, but I could not think of an example where that might be useful. Then, I decided that it did have a useful purpose if it resulted in a desirable display result. So the first example is:

=TEXTJOIN(A7:D7, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula =CHAR(10).

As you can see in the figure, each record appears to be on a single line in the cell (F9).


This same technique can be enhanced further in this way.

=TEXTJOIN(A8:D8, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula


With the TEXTJOIN formula example in cell H9, the appearance of the created string makes it very easy to see (in the following figure) each record.


The example file can be downloaded here.