What is Advanced Excel
Advanced Excel, to most people, is defined simply as knowing more
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
At the next Level 4, he will be able to make use of the dollar sign at will,
At Level 5, the use of the dollar sign to calculate running total for the range of numbers. starting from the first cell,
At Level 6, he will be able to freeze the dollar sign either on the row or column on both sides of the range,
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
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)
At Level 9, SUMIFs is used to calculate the total between 2 values,
At Level 10, Excel users will be able to create formulas that allow the conditions to be placed outside the formula in another cell,
Beyond Level 10, Users will learn how to combine one formula with another to create a Dynamic formula that makes it
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
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
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.