In today's constant changing business environment, business operation or finance people are expected to offer new prompt insights to the business regularly. In front of large amounts of data, with the limited formulas and functions knowledge in Excel is not enough to accomplish the job, they have to know the how to use right way to analyse the data, identify the KPIs, patterns and trends and provide forecasting for the future. This can only be achieved if they possess advance Excel skills.
in this article, we have not explained each single technical tips and tricks, as Google searches provide so many good tutorials
Learn to use Lookup
formula
In most excel users' experience, the Lookup formulas are the most popular formulas they use. So often during our daily work we deal with data from multiple sources which in some way or another need to be combined so that we can make more sense of it. For example, if you are a school teacher and you have two different spreadsheets with same group of students, one spreadsheet is their English test results and the other one is Mathematics test results, with Lookup formula, you are able to merge two spreadsheets into one single spreadsheet to show both results based on students their ID or names. Sometimes they're not always the easiest of formula to understand, but a decent grasp of them can make the difference between data novice and spreadsheet master.
Learn to manipulate text
Text manipulation formula might not be something you will use every day, but a good understanding of the basics will definitely come in useful at some point in your career. The formula below are the ones you should know (and can be extremely powerful if they are nested within each other, in our experience, the most use text formula are: Left, Right, Len, Mid, Find
For example, if you have a spreadsheet with students' name like, "Smith, John" or "Jack Lee" or even with middle name such as "Homer Jay Simpson" and you wish to sort them by family name or first name. you can easily use Find formula to divide them with family name and first name in two columns then sort them(actually there is other quick way to do it by using "text to columns" under Data menu in Excel 2007 or higher version, but in this topic we tried to use text formula here) . Let me give you another example, say, if you have a employees list with their date of birth format "dd/mm/yyyy" and you want to find out how many people were born in same month or same year and who they are, with the formulas above you are able to separate their DOB into three different columns by day, month and year then easily sort them out.
Once again there are many more, but I would say that, used in conjunction with each other, the five formula above can give you a massive amount of control over any text data you can throw at them.
Learn to use an IF statement
People are afraid of functions but you must learn the IF statement because it comes in extremely handy, so often. There are 3 parts to an IF statement :IF(something true or false, what gets entered into a cell if the first value is true, what gets entered into a cell if the first is false).
Let’s say we want to have a summation of how many students passed or failed an exam, and 60 or above is a “Pass.” One column is a person’s final score (the Grade column), another column lets us know their status. Use the formula IF(Grade > 60, “Pass”, “Fail”).
Then you could do a pivot table on this and sum up automatically the number of passes and fails.
Learn to use Pivot Table
Without a doubt, pivot tables are the most powerful feature in Excel since it was created decades ago. A pivot table is an Excel tool for summarizing a list into a simple format. You create pivot table from lists, as you define which fields should be arranged in columns, which fields should become rows, and what data you wish to summarize. You don't have to use all of the data in a spreadsheet--just the data and the fields you need to answer your questions. Once you've created the table, you can then see the answer to your question right away. You can later reuse the pivot table to answer different questions by rearranging it. In other words, pivot table give you much more freedom in excel and enable you to look at data from different perspectives by simple clicks.
Here is an example any reader might relate to. Let us say if you are a sales analyst for a consumer electronics stores group which has more than 20 stores across the country, every day you download sales reports of each store from your mainframe system and you are trying to divide up the country into 6 region, say R1, R2, R3, R4, R5 and R6. What people used to do is manually add up a bunch of stores in each region into one worksheet and tag it with region name, sort or filter them and do a sum of the store individual revenue to get the total for that region, that would be OK, but if your boss wants to divide up those regions to East Coast and West Coast and compare them, then you will have to redo the entire exercise again…. Would you have to spend hours to work it out?
With a pivot table, if you have a column that has regions spelled out, you can pivot it with a single click and see different combinations.............and it is only 10 minutes job!
Let us go a bit further, same reports and this time your sales manager wants you to draw a picture in numbers to show the total number of Andriod or iOS tablets sold for each brand and each model(16GB/32GB/64GB with 4G or WiFi only) in particular day(Boxing day sale) or period of time(Christmas sales season) in all of stores or the stores in Syd and Mel two cities only or top 5stores in each region, in addition he wants to compare them stores by stores and compare the results of this year with same period of last year as well, the purpose of this exercise is to provide information for consumer trend study and for 2013/14 group sales budgeting as well... obviously what you are going to deal with is a multidimensional data set with hundreds or even thousands rows data inside, if you still use the way as mentioned earlier to manually add up, sort and run the filter, it could take you hours or days to work it out and the problem is the report you provide is fixed format, meaning, whenever you boss wants to look at results from different perspectives and you have to redo the exercise again.. and again..
However with pivot table, if you get everything ready in columns, you will be able to manipulate data and show the results by a couple of clicks and reports can be transformed by any formats as you want and you are able to present your data with dynamic pivot chart.
Learn to use keyboard shortcuts
Many Excel advanced users will tell you one thing: learning to use keyboard shortcuts will reduce down the amount of time you spend on navigating and selecting your data in Excel and greatly speed up your work in Excel, simply google excel shortcuts and you will find heaps of tutorials for keyboard shortcuts.
Learn to use charts to present
TV show “Master Chef” series have told us one thing: when it comes to a good food, both taste and presentations are important, sometimes the latter even is a deciding point. Similar to business report, if you find out some interesting points behind the scene or you have a great idea about how to improve your profit drivers and you pull all the figures into a spreadsheet to show your boss or investors and most of time it will not work, why? Bear in mind, not everyone came from same background as you and in front of many non finance people, your reports are just purely boring numbers made up from 0 to 9 and don’t mean anything, but if you are able to use various types of charts or dashboard to visualize your reports(like cover picture of this article), and the result might be different. In one word, by using charts, you can communicate effectively and present results in a stunning manner.
Graphs and charts are really quite complex if you want them to be, but the basics often will suffice and good enough to your work, here are a couple of things you should learn:
a. How to select right type of chart for any situation
b. Ability to set up dynamic & interactive charts/ combine various charts into one (we will discuss it in future, and believe it or not, it will totally change your view about Excel!)
c. Use features like conditional formatting charts
In most excel users' experience, the Lookup formulas are the most popular formulas they use. So often during our daily work we deal with data from multiple sources which in some way or another need to be combined so that we can make more sense of it. For example, if you are a school teacher and you have two different spreadsheets with same group of students, one spreadsheet is their English test results and the other one is Mathematics test results, with Lookup formula, you are able to merge two spreadsheets into one single spreadsheet to show both results based on students their ID or names. Sometimes they're not always the easiest of formula to understand, but a decent grasp of them can make the difference between data novice and spreadsheet master.
Learn to manipulate text
Text manipulation formula might not be something you will use every day, but a good understanding of the basics will definitely come in useful at some point in your career. The formula below are the ones you should know (and can be extremely powerful if they are nested within each other, in our experience, the most use text formula are: Left, Right, Len, Mid, Find
For example, if you have a spreadsheet with students' name like, "Smith, John" or "Jack Lee" or even with middle name such as "Homer Jay Simpson" and you wish to sort them by family name or first name. you can easily use Find formula to divide them with family name and first name in two columns then sort them(actually there is other quick way to do it by using "text to columns" under Data menu in Excel 2007 or higher version, but in this topic we tried to use text formula here) . Let me give you another example, say, if you have a employees list with their date of birth format "dd/mm/yyyy" and you want to find out how many people were born in same month or same year and who they are, with the formulas above you are able to separate their DOB into three different columns by day, month and year then easily sort them out.
Once again there are many more, but I would say that, used in conjunction with each other, the five formula above can give you a massive amount of control over any text data you can throw at them.
Learn to use an IF statement
People are afraid of functions but you must learn the IF statement because it comes in extremely handy, so often. There are 3 parts to an IF statement :IF(something true or false, what gets entered into a cell if the first value is true, what gets entered into a cell if the first is false).
Let’s say we want to have a summation of how many students passed or failed an exam, and 60 or above is a “Pass.” One column is a person’s final score (the Grade column), another column lets us know their status. Use the formula IF(Grade > 60, “Pass”, “Fail”).
Then you could do a pivot table on this and sum up automatically the number of passes and fails.
Learn to use Pivot Table
Without a doubt, pivot tables are the most powerful feature in Excel since it was created decades ago. A pivot table is an Excel tool for summarizing a list into a simple format. You create pivot table from lists, as you define which fields should be arranged in columns, which fields should become rows, and what data you wish to summarize. You don't have to use all of the data in a spreadsheet--just the data and the fields you need to answer your questions. Once you've created the table, you can then see the answer to your question right away. You can later reuse the pivot table to answer different questions by rearranging it. In other words, pivot table give you much more freedom in excel and enable you to look at data from different perspectives by simple clicks.
Here is an example any reader might relate to. Let us say if you are a sales analyst for a consumer electronics stores group which has more than 20 stores across the country, every day you download sales reports of each store from your mainframe system and you are trying to divide up the country into 6 region, say R1, R2, R3, R4, R5 and R6. What people used to do is manually add up a bunch of stores in each region into one worksheet and tag it with region name, sort or filter them and do a sum of the store individual revenue to get the total for that region, that would be OK, but if your boss wants to divide up those regions to East Coast and West Coast and compare them, then you will have to redo the entire exercise again…. Would you have to spend hours to work it out?
With a pivot table, if you have a column that has regions spelled out, you can pivot it with a single click and see different combinations.............and it is only 10 minutes job!
Let us go a bit further, same reports and this time your sales manager wants you to draw a picture in numbers to show the total number of Andriod or iOS tablets sold for each brand and each model(16GB/32GB/64GB with 4G or WiFi only) in particular day(Boxing day sale) or period of time(Christmas sales season) in all of stores or the stores in Syd and Mel two cities only or top 5stores in each region, in addition he wants to compare them stores by stores and compare the results of this year with same period of last year as well, the purpose of this exercise is to provide information for consumer trend study and for 2013/14 group sales budgeting as well... obviously what you are going to deal with is a multidimensional data set with hundreds or even thousands rows data inside, if you still use the way as mentioned earlier to manually add up, sort and run the filter, it could take you hours or days to work it out and the problem is the report you provide is fixed format, meaning, whenever you boss wants to look at results from different perspectives and you have to redo the exercise again.. and again..
However with pivot table, if you get everything ready in columns, you will be able to manipulate data and show the results by a couple of clicks and reports can be transformed by any formats as you want and you are able to present your data with dynamic pivot chart.
Learn to use keyboard shortcuts
Many Excel advanced users will tell you one thing: learning to use keyboard shortcuts will reduce down the amount of time you spend on navigating and selecting your data in Excel and greatly speed up your work in Excel, simply google excel shortcuts and you will find heaps of tutorials for keyboard shortcuts.
Learn to use charts to present
TV show “Master Chef” series have told us one thing: when it comes to a good food, both taste and presentations are important, sometimes the latter even is a deciding point. Similar to business report, if you find out some interesting points behind the scene or you have a great idea about how to improve your profit drivers and you pull all the figures into a spreadsheet to show your boss or investors and most of time it will not work, why? Bear in mind, not everyone came from same background as you and in front of many non finance people, your reports are just purely boring numbers made up from 0 to 9 and don’t mean anything, but if you are able to use various types of charts or dashboard to visualize your reports(like cover picture of this article), and the result might be different. In one word, by using charts, you can communicate effectively and present results in a stunning manner.
Graphs and charts are really quite complex if you want them to be, but the basics often will suffice and good enough to your work, here are a couple of things you should learn:
a. How to select right type of chart for any situation
b. Ability to set up dynamic & interactive charts/ combine various charts into one (we will discuss it in future, and believe it or not, it will totally change your view about Excel!)
c. Use features like conditional formatting charts
(We have recently worked on
a project to create a mobile dashboard reporting using a new technology on iOS devices.
Imagine in one day, you could present your reports by using iPhone/iPad and it
allows you to deliver interactive information to the users by anytime and
anywhere. We will discuss it later in our blog here)
Learn to use conditional
formatting
Conditional
formatting is not simply decoration for the report, conditional formatting is
able to highlight the items with various colors in different value so report
reader can instantly identify items on paper or on screen, for example, you are
a hotel group sales executive and the operation manager wants to know
so far how many hotels under the group have sold more than 80% rooms for the
next coming weekend and what type of rooms they are, you can easily highlight
hotel names and type of rooms with different color instantly by using conditional
formatting without running any sorting or filter in your spreadsheet.
Learn to import text format file
Image you have
a statement or documents in PDF or text format, such as electricity bill or
phone bill and you wish to take a deep look at the costing and perform some analysis
on it, what you could do in old fashion way is, manually type it into spreadsheet
and do further data manipulation, we say there is no problems with that, but
think about how much time and effort you have to spend on it, do you know there
is a better efficient way to get this job done by using “Text Import Wizard” in
Excel to import these files? Follow instructions as you go through the steps in
the Wizard to identify which row at which to begin the import and specify which
delimiter is used. You also can change data types for certain types of
data or you can skip the import for certain columns. You can also preview the
data to make sure that it will be imported correctly.
Learn to use what if analysis
This is an interesting part as it
might relate to our personal life at some point or another, suppose
you are willing to buy a new property, upgrade your car, plan an overseas
holiday or as a corporate, your company is willing to buy another business and therefore you wish to
work out a way in spreadsheet table that be able to show the different financial scenarios
you might have and what is the consequences it will bring as a result financially.
This is where what if analysis comes in. What-if analysis is the process of
changing the values in cells to see how those changes will affect the outcome
of formulas on the worksheet. There are three kinds of what-if analysis tools
come with Excel: scenario manager, data tables and Goal Seek.
Goalseek is you have desire result from the formula
and you are not sure input value to achieve the result. For example, if you
need to borrow some money to buy a car, the loan amount and repay term is
fixed, but interest rate will be variable, then using goal seek you will find
out different repayment amount based on different interest which the lender can
offer. If you log on some car makers or finance companies their website, e.g. Lexus, you will find they provide finance calculator basing on this tool which is quite handy.
A scenario is a named what-if model that includes variable cells linked together by one or more formulas. Before you create a scenario, you must design your worksheet so that it contains at least one formula that’s dependent on cells that can be fed different values. the Scenario Manager helps you keep track of multiple what-if models. Using the Scenarios command on the Tools menu, you can create new forecasting scenarios, view existing scenarios, run scenario management commands, and display consolidated scenario reports.
If your Excel skills are up to here, congratulations, you are almost there, hang on a sec....., we are not saying you are Excel guru now, but at least knowing how to use VBA can prove that you have known a lots things in Excel and having knowledge about doing some programs can really dramatically increase efficiency and productivity, especially when you handle a large data, for example, running a payroll reports for hundreds employees or sales reports for thousands of stocks, although some people might insist that the systems you have may enable you to customize the reports and get reports straightaway from there, but in many cases you still have to perform a mundane, repetitive steps till eventually getting a designated report. With VBA, it gives you a shortcut and make those repetitive steps become a single click and get it done.
In these day, many BI software are gradually replacing of position of Excel to the business, but not matter what kind of advanced software being used, from the moment of the data coming out of software to be presentable to management for their decision making, Excel still plays the vital role during this process.
A scenario is a named what-if model that includes variable cells linked together by one or more formulas. Before you create a scenario, you must design your worksheet so that it contains at least one formula that’s dependent on cells that can be fed different values. the Scenario Manager helps you keep track of multiple what-if models. Using the Scenarios command on the Tools menu, you can create new forecasting scenarios, view existing scenarios, run scenario management commands, and display consolidated scenario reports.
Learn some VBA skills
If your Excel skills are up to here, congratulations, you are almost there, hang on a sec....., we are not saying you are Excel guru now, but at least knowing how to use VBA can prove that you have known a lots things in Excel and having knowledge about doing some programs can really dramatically increase efficiency and productivity, especially when you handle a large data, for example, running a payroll reports for hundreds employees or sales reports for thousands of stocks, although some people might insist that the systems you have may enable you to customize the reports and get reports straightaway from there, but in many cases you still have to perform a mundane, repetitive steps till eventually getting a designated report. With VBA, it gives you a shortcut and make those repetitive steps become a single click and get it done.
In these day, many BI software are gradually replacing of position of Excel to the business, but not matter what kind of advanced software being used, from the moment of the data coming out of software to be presentable to management for their decision making, Excel still plays the vital role during this process.
Blog by C. Yang
No comments:
New comments are not allowed.