20 Tricks That Can Make Anyone An Excel Expert?
(Second part.) MS Excel and similar office, business software packages are very complicated and in reality you can’t find 20, nor 30 tricks and say then automatically “I’m an expert”. I think the solution is learning how to solve problems and how learn every new tricks and features you meet, or you can need.
Social needs, individual researches
For example, for some people it is very important how tables, reports, queries look in Excel or Word, they need to master the formatting in the software. Fort others, this is not important, for example for speculators or entrepreneurs making analysis for their own, to support their own investment decisions.
And others can use the built-in functions, templates, styles, format painter to give a readability-friendly look to the sheet or chart, but they need only the standard tools. Some need only basic functions and some use the advanced financial or the database functions. Some are making charts all the time, others are calculating accounting tables or statistical indicators.
Dozens of guru builders
The last time I gave you 9 advice, now I owe you 11 more. But the best advice is, I think, to teach you to find the solutions in every situation. There are at least 20-30 posts with a similar title (20 Tricks That Can Make…). Different hotkeys, shortcuts, mouse clicks, searching methods etc.
From two to three dimensions
All seem to be good ideas, but I’m not sure I need them. Maybe I need something different. Some time ago, for example I needed three-dimensional lookup. I often use functions “vlookup” and “hlookup” to extract a value from a table. For example, here:
There are two dimensions, companies, and countries. Rows and columns. But if you complete it with a third dimension, or field, the year, you have an advanced, 3 dimensional table:
If you search for “3 dimensional lookup” or “three-way lookup”, you find for example, this 7 minutes video and a simple trick. You can easily concatenate (join) the two criteria, with the “&” sign and search for this new string in the array. (Or, you can use a pivot table first.)
Not only hotkeys or mouse clicks
If you look at the video again you see it is the “Excel Magic Trick 781” – incredible how many Excel teachers and lessons are there on Youtube. Thousands of tutorials, you only have to find the right one. If you have problems with financial functions, you could begin here, if you need to learn pivot tables, you can do it here.
I recommend you also this article of Business Insider, teaching some important advanced functions, and not only hotkeys or mouse strokes like many other posts.