Excel lets you type a formula directly into a cell. Or, you can select a cell and then type the formula in Excel’s edit box. However, it’s much easier to create a complicated formula for Excel by doing it in another program and then doing a copy-and-paste to put it into Excel.
Notepad works perfectly from the text manipulation point of view. But, Notepad++ is designed as a programmer’s editor (and it’s free, too!).
For an Excel formula, Notepad++ helps us write our formulas by allowing us to insert carriage returns so we can follow our logic as we try to create it, and by color-coding the parentheses pairs, similar to the way Excel does.
Another cool feature helps keep you on track as you are creating your formula — as you type a parenthesis, the matching one highlights, too. By breaking the different test conditions of an IF statement into multiple lines, you can see if you’ve skipped a step.
As we finish he formula, we can see that the parentheses match. The red box in the tab (you can have multiple tabs with multiple files being edited) shows you that this work has not been saved yet.
Our last step in Notepad++ is to select the formula that we’ve created, so we can copy it to paste into Excel.
After highlighting the formula to select it, copy it by Control-C or using he Edit > Copy function on Notepad++’s menu bar.
Now, the fun part starts — with pasting the formula into Excel.
The obvious place to paste the formula is directly into the cell in which we want the formula to generate its answer. We can see the effect of pasting the formula into cell G4 by looking at cells G4, G5 and G6. As we can see, by pasting it that way, Excel broke the formula into three lines. The first has only an IF test. Excel made an automatic correction and added a closing parenthesis, and gave us a result of 0. Cells G5 and G6 show the text that was in the other two parts of the formula.
By selecting cell G10 and then pasting the formula directly in the formula edit box at the top of the spreadsheet, we’ll get what we want…
In this last image, I’ve selected cell G10. We see that we have a readable formula in the formula edit box — and we have an answer in cell G10. I never filled in anything in the four cells being tested, so we see that IF test results that tell us that A4 matched A5 and B4 matched B5.