What is Advanced Excel

Advanced Excel, to most people, is defined simply as knowing more formulas and functions in Excel. But for us, advanced Excel takes on a different definition. It is about maximizing the use of each and every formula and function. Allow me to explain what we mean with summation.

If we start with Level 1 for a basic user who never touch Excel before, the first thing he is likely to learn is adding up numbers inside a cell in the following way:

= 4 + 5 + 6

At level 2, the user will learn that he can add numbers from different cells, =B2+C2+D2.

At Level 3, he learnt that there is a formula called SUM where he can add up the numbers all at one go,
=SUM(B2:D2).

At the next Level 4, he will be able to make use of the dollar sign at will,
=SUM($B$2:$D$2).

At Level 5, the use of the dollar sign to calculate running total for the range of numbers. starting from the first cell, =SUM($B$2:D2).

At Level 6, he will be able to freeze the dollar sign either on the row or column on both sides of the range,
=SUM(B$2:$D2).

At Level 7, he will need to learn summation based on a single condition will kick. That is when he learns about the basic of SUMIF formula, summation based on a single value. This invovlves multiple rows of cells
=SUMIF(B2:B100,500).
And our definition of Advanced formula starts from here.

At level 8, there will be a need to do multiple conditional SUM and that requires the use of SUMIFs. At this level, he will learn how to use SUMIF formula to calculate the values in a specific range, =SUMIF(C2:C100,"company name",B2:B100)
or
=SUMIF(B2:B100,">=100").

At Level 9, SUMIFs is used to calculate the total between 2 values,
=SUMIFs(B2:B100,B2:B100,">100",B2:B100,"<500").

At Level 10, Excel users will be able to create formulas that allow the conditions to be placed outside the formula in another cell,
=SUMIF(B2:B100,">="&E2).

Beyond Level 10, Users will learn how to combine one formula with another to create a Dynamic formula that makes it maintenance free, =SUMIF(Offset(B2,0,0,E3,1),">="&E2.

One example is related to data downloaded from SAP. When you export data containing dates from SAP to excel, you may notice that the dates are formatted as text. Most people will put the blame on Excel, not knowing that it is caused by their computer setup. But whatever the cause is, Excel does offer a solution to the problem. And if you want to know what the solution is, go to our page converting text dates from SAP to excel dates.

Another of our favorite example is our Excel calendar, created only with advanced Excel formulas, no Macros or VBA is involved. And it is more powerful than most of the solutions you can find on the internet. You can see the 12 months calendar of any year simply by one click of the mouse button. Not only so, you can enter your own country holidays into the template and it will be highlighted automatically for you without you having to do it manually. If you wish to highlight special occassions, such as your spouse and children's birthdays, family vactions dates, you can just enter them into a cell and the Excel Calendar will help you colour the calendar automatically. You can now download a FREE copy of the calendar in our Excel calendar page.

Another function which has also impressed me is the recording of a macro function. With this function, we can create a macro without the need to know about programming. The codes are generated automatically by Excel or to be more specific, Visual Basic for Applications. Take a look the web page on macro Excel Visual Basic for Application (VBA) on how I have used recording a macro to create a cell formatting macro.

A lot of advanced excel users have misunderstood Visual Basic for Application for Visual Basic. They are different. Visual Basic (VB in short) is not an abbreviation (or short form) for Visual Basic for Application. To be a good Visual Basic programmer, you need to understand how use those codes in a program. However, to be a good VBA programmer, you need to know the programming codes as well as the Excel formulas and functions. Excel VBA has the capability to enhance the power of the Excel. You can even use VBA to create functions that are not available in Excel.

•

New! Comments

Have your say about what you just read! Leave me a comment in the box below.