Spreadsheets lie at the center of excel. It can store millions of data cells, and multiple worksheets at once. But just data storage is not what has made excel what it is. The wide range of expressions and formulae are what makes excel so dynamic. From simple sum and average functions to risk calculating expressions which take the risks of multiple assets into account and give a single number as output can be written in a single cell. You can have basic data stored in a sheet and refer them in entirely different sheets or even files. For managers, it is handy when you have to keep data from various sources separate and generate an analysis in a different file. As the formulae are dynamic, the cell calculations are updated automatically when you change the data in the cells.
You can make a model for data of a given year and keep on replacing it with data from different years to get progressive analysis. Or say you have the quotations from different vendors for a project, as long as the template of data remains same, you can just change the source of data and compare which vendor gives you the best ROI.
Then there is the option to sort data. Both text and numbers can be sorted using a wide range of rules. You can also apply multiple rules to the same data. Say sort the countries alphabetically, then in those countries sort the cities by land area and so on. The sorting option is a very powerful tool.
Data visualization is handy when you are dealing with a huge chunk of data. The graphs in excel provide a wide range of options on how the data can be displayed. From simple line graphs, which are suitable for showing trends, to combo charts where one metric is shown using histograms and the other one using box and whisker, excel can do it all. There are as many formatting options for each chart as there is the type of charts.
Another exciting aspect of excel is pivot tables. Here you can see the intersection of data. It allows you to club data in your range by various rules like frequency, average, year, etc. For example, you have a table with transaction date and transaction amounts for a departmental store over a year. Not how do you see the total transactions and a transaction amount for each month? This can be done quickly by a pivot table. Now you can target months when you had low sales or when there were high sales but a small number of transactions.
There are more sophisticated features like goal seek. What annual rate of interest do you need to double your money in 58 months if your interest is compounded annually. Goal seep tool will be useful here. Set the amount to the double of your principle. Ask the tool to change your rate of interest, and it will give you the required interest rate.
A similar tool is the solver but much more sophisticated. Fin the maximum or minimum of a model by changing specific cell (or maybe 20 cells) keeping a few cells constant. You can even set rules on the cells you are changing like 5 of those cells have to be always positive. One has to be greater than the other. It can be a nonlinear expression and excel will give you the result.
Then there are macros that help you create functionality in excel which was not provided by Microsoft. You can even make internet call using macros, save files at different locations and also design games (though I wouldn’t suggest that you make this last one a habit).
There is a reason that best of platforms like Bloomberg and DataStream have an excel plugin. Excel it very dynamic and adaptable. At the same time extremely secure. If used correctly, you can achieve wonderful results for your business. After two months of internship, excel is one tool that I will want to learn more about.
Editor's Note :
Now that you have realised that there is no escaping Excel, what is it that you can do to hone your skills and become a pro at Excel? We have got you covered. Check out our Excel Course on Konversations.com. Check out the free sample here.
Chat
Comments