Claudio Merlini
New Member
I’d like to share with you an idea I came up with, that in my opinion is pretty cool.
I’ve done researches through the forum but I haven’t found anything precisely like that.
Maybe you, as pro, could confirm it’s original by me or more likely I’m the only one on the planet that didn’t know it…anyway I think it deserves a chance.
The idea is actually very simple, use the excel autocorrect option to enter formulas.
The advantages are easily understandable:
Ø you can complete relatively complex tasks with only one button in every worksheet, workbook or file opened. It’s always available, nothing to rewrite, load or path to refer to.
Ø you don’t need to use VBA, enable macro, fill the QAT of buttons etc.
Ø you can avoid the limitation of names that they work fine inside the workbook where you’ve created them, but you have to recreate/copy them (except doing a template) or to specify the path if you want to use them in other workbooks.
I’ll show you a couple of applications to make this clear.
If you have a table like that (Fig. 1) and you want to have a column with the Name+Surname you could use the classic CONCATENATE (&) function, but, if you are superlazy as me, you could use only the symbol “♣” and you’re done. How? EASY!
As first step make the symbol “♣” in a cell pressing ALT + 5 (Keypad number), select and copy it (Fig .2), then open the autocorrect option in excel menu – options (Fig. 3), paste it in “Substitute” field and then in “With” field enter the following formula:
=INDIRECT("RC[-2]"; )&" "&INDIRECT("RC[-1]"; ) and you’re done! (Fig. 4)
This works because every time you digit the symbol “♣” Excel substitute it with the correspondent formula, a formula that works in every worksheet/workbook you’re working on. It always works, you don’t need to repeat the procedure after saving or to create a template.
Notes:
Ø I’ve used the ALT+5 combo as memory trick, since to reverse the process I’m used to press ALT+4 (left arrow ←) to obtain the Name and ALT+6 (right arrow→) to obtain the Surname. But you can use any word/character/symbol you want (included the already ones existing on the keyboard: § etc. – Fig. 5).
Ø I’ve translated the formulas in English from my Italian Excel version, so you maybe have to change the “;” to “,” to make them work. Also I had to add a small blank space between ";" and ")" to avoid the annoying emoticons, sorry for that and please not consider it you copy & paste the formulas.
Ø I prefer to use the R1C1 reference with indirect function because in my opinion are shorter/easier and less memory demanding than the A1reference (the syntax for the concatenate case would become INDIRECT(ADDRESS(ROW();COLUMN()-2;4;; ))&" "&INDIRECT(ADDRESS(ROW();COLUMN()-1;4;; ))
Ø This technique doesn’t affect the ALT combos of QAT
Ø If you want you can turn it off. (Fig. 6)
Another interesting application of autocorrect option to enter formulas technique is to always refer to the cell above. As you know this comes in handy in several situations. The classic case is when you have a sum of a range of cells. If you (or better an inexperienced user) add a row between the last record and the sum formula you’ve to manually update the reference of sum formula to make it works correctly. To avoid this you can use this technique (Fig. 7).
I used the symbol “◘” = ALT + 8 (up arrow ↑) as memory trick but you could write for example the word “cabove” to remember it, if you prefer (Fig. 8).
As you know you can do this with names too. The problem is, as I told before, that the names work great inside the file in which you’ve created them, but to use them in another file they require some work.
Besides the examples I showed here, I think that the idea to enter formulas using the Excel autocorrect option has really endless applications, great potential and above all is very fun!
I use it a lot at work and it saves me a lot of time.
I hope you enjoy it too.
I’ve done researches through the forum but I haven’t found anything precisely like that.
Maybe you, as pro, could confirm it’s original by me or more likely I’m the only one on the planet that didn’t know it…anyway I think it deserves a chance.
The idea is actually very simple, use the excel autocorrect option to enter formulas.
The advantages are easily understandable:
Ø you can complete relatively complex tasks with only one button in every worksheet, workbook or file opened. It’s always available, nothing to rewrite, load or path to refer to.
Ø you don’t need to use VBA, enable macro, fill the QAT of buttons etc.
Ø you can avoid the limitation of names that they work fine inside the workbook where you’ve created them, but you have to recreate/copy them (except doing a template) or to specify the path if you want to use them in other workbooks.
I’ll show you a couple of applications to make this clear.
If you have a table like that (Fig. 1) and you want to have a column with the Name+Surname you could use the classic CONCATENATE (&) function, but, if you are superlazy as me, you could use only the symbol “♣” and you’re done. How? EASY!
As first step make the symbol “♣” in a cell pressing ALT + 5 (Keypad number), select and copy it (Fig .2), then open the autocorrect option in excel menu – options (Fig. 3), paste it in “Substitute” field and then in “With” field enter the following formula:
=INDIRECT("RC[-2]"; )&" "&INDIRECT("RC[-1]"; ) and you’re done! (Fig. 4)
This works because every time you digit the symbol “♣” Excel substitute it with the correspondent formula, a formula that works in every worksheet/workbook you’re working on. It always works, you don’t need to repeat the procedure after saving or to create a template.
Notes:
Ø I’ve used the ALT+5 combo as memory trick, since to reverse the process I’m used to press ALT+4 (left arrow ←) to obtain the Name and ALT+6 (right arrow→) to obtain the Surname. But you can use any word/character/symbol you want (included the already ones existing on the keyboard: § etc. – Fig. 5).
Ø I’ve translated the formulas in English from my Italian Excel version, so you maybe have to change the “;” to “,” to make them work. Also I had to add a small blank space between ";" and ")" to avoid the annoying emoticons, sorry for that and please not consider it you copy & paste the formulas.
Ø I prefer to use the R1C1 reference with indirect function because in my opinion are shorter/easier and less memory demanding than the A1reference (the syntax for the concatenate case would become INDIRECT(ADDRESS(ROW();COLUMN()-2;4;; ))&" "&INDIRECT(ADDRESS(ROW();COLUMN()-1;4;; ))
Ø This technique doesn’t affect the ALT combos of QAT
Ø If you want you can turn it off. (Fig. 6)
Another interesting application of autocorrect option to enter formulas technique is to always refer to the cell above. As you know this comes in handy in several situations. The classic case is when you have a sum of a range of cells. If you (or better an inexperienced user) add a row between the last record and the sum formula you’ve to manually update the reference of sum formula to make it works correctly. To avoid this you can use this technique (Fig. 7).
I used the symbol “◘” = ALT + 8 (up arrow ↑) as memory trick but you could write for example the word “cabove” to remember it, if you prefer (Fig. 8).
As you know you can do this with names too. The problem is, as I told before, that the names work great inside the file in which you’ve created them, but to use them in another file they require some work.
Besides the examples I showed here, I think that the idea to enter formulas using the Excel autocorrect option has really endless applications, great potential and above all is very fun!
I use it a lot at work and it saves me a lot of time.
I hope you enjoy it too.
Claudio Merlini
- Italian Excel Enthusiastic -