How to Improve Excel Skills Quickly and Effectively

The driver is: laziness. The trick is: refine your day by day on the path to efficiency, robustness, and aesthetics. The mantra is: if the same or similar a thing for the third time to do, be sure to optimize the existing solution. To summarize: keep looking for better solutions in practice, and then master them. Solution exists in the shortcut keys, functions, advanced tools and VBA. First, the shortcut keys in fact, do not have to deliberately memorize the shortcut keys, as long as the practice of Excel repeatedly encountered some slightly complex operations (especially those operations that require the use of the mouse), go to check if there are shortcuts, and then continue to use the natural memory. The following list of some extremely common and inevitable encounter. Another fast and furious way to force yourself to memorize shortcuts is to unplug the mouse. The most commonly used and must be remembered Ctrl + C / V / X / S / F: do not want to explain, they go to check, these five must be skilled in the beginning to move the focus of Ctr + ↑ ↓ ← → : switch to the content area of the up and down the left and right end Ctr + Shift + ↑ ↓ ← → : switch to the content area of the up and down the left and right end of the region and select the region Ctrl + PgUp / PgDn Ctrl+PgUp/PgDn: switch from left to right (right to left) between worksheet tabs Edit Formatting Alt+E+S: bring up the "Selective Paste" screen Ctrl+Shift+%: use the "percent" format without decimal places Ctrl+Shift+^: use the "percent" format with decimal places Ctrl+Shift+^: use the "percent" format with decimal places Ctrl+Shift+^: use the "percent" format with decimal places Shift + ^: use the scientific notation format with two decimal places Ctrl +;: enter the current date Ctrl + B: use or cancel the bold formatting Ctrl + U: use or cancel the operation of the underline Ctr + Y / Z: Repeat or undo the last operation Alt + I + W: insert a new worksheet Alt + F11: open the VBA editor (VBA is a favorite, see) Excel in the end how powerful? - (He Mingke's answer) Second, the function when the demand is not complex, with "=" or "sum" and so on is enough. However, with the escalation of demand, VLookup came. Vertical lookup after the horizontal lookup (HLookup) and a variety of fancy lookup (Match/Offset/ Indirect/Address). After the lookup, it involves statistical calculations, so the need for Sumif or Sumifs came. However, this is not enough, will gradually multiply the demand for multiple complex conditions similar to the database Select, so it had to introduce an array function. For example: to calculate the total value of product AA, instead of select sum(product quantity x product unit price) from ... where product number = 'AA'. Of course the use of array function is very simple and flexible: {=SUM (IF (($B $4: $B $8 = "AA"), ($C $4: $C $8) * ($D $4: $D $8), 0))} } In addition, engage in the financial and financial students, must have done three statements, and certainly experienced the sourness of the statements to match between the flat. In order to be lazy and improve the robustness of the financial model, all kinds of index functions and array functions above to the extreme, so the realization of automatic leveling and checking. Third, the higher-order tools Excel has many high-level tools can greatly improve the efficiency and beauty. First of all, charting tools, because as a visual animal, humans increasingly need to use charts to understand and express. In the article (RIO is how to sweep north and south of the river? - Data Iceberg - 知乎专栏), in order to express the year-on-year growth of cocktail consumption willingness, the data was extracted and then needed to be expressed using visualization. The left side of the figure below is a chart drawn using the default system configuration, is it a strong square dance style? To achieve the financial magazine style on the right of the figure below, it is necessary to optimize one parameter after another: optimize the chart type, configure Title, label and data source, add trend lines, optimize fonts and so on. In order to make the chart look better, a variety of tricks and parameters are used once for Excel charting tools, naturally mastered. At the same time, usually collect those big financial magazines of the classic form (for example: the Wall Street Journal, the Economist, etc.), and then look for textbooks and training courses to achieve these forms once again, so that the function of Excel charting is more familiar. In addition, Excel has a lot of data cleaning (sorting, screening, according to the uniform separator to separate data, etc.) and analytical tools, some of which are very high-level (ANOVA and multiple linear regression, etc. are not a few), in the process of gradual use and refinement, but also gradually mastered. Fourth, VBA VBA actually do not have to learn the system, but first directly with the experience of the computer more efficient than the man's sense of pleasure, and then in practice to constantly explore and try more advanced skills, only to encounter bottlenecks, and then look for a book to see. The first time I used VBA because I have to repeatedly print numerous documents, and the print order and single and double-sided requirements are very strict, very easy to make mistakes. So can not endure, but limited to skills, can only use macros to record the process of constant repetition, and then simply change the parameters, and finally through the implementation of macros. Then out of hand, as the printing problem is constantly complex, the program is subsequently upgraded, VBA skills have also soared. Encountered some advanced needs are as follows, but also mastered a lot of printer settings, Worksheet switching and call the operating system API and so on a variety of skills How to prevent paper jams? How to coordinate multiple printers to increase speed? How to automatically select the nearest printer in a large office? ...... Another example is the creation of animated GIFs of charts and graphs for a more aesthetic and cool look. In the beginning, an online program was used to color a map of China, which made it easier to create a GIF for How did cassoulet rice catch fire? - He Mingke's answer to create dazzling tables. Later, in order to spread on WeChat has a cooler effect, try to make GIF. so began to look for the production of GIF external program, and ultimately the use of VBA to call the external program as well as read and write the system file of various skills to complete the automatic generation of GIF animation. Fifth, to build a model of the set above are techniques, Excel's greatest practical value is to produce various types of financial models or simple mathematical models, with the correct way to do the model (the so-called "set") is the heart of the law. In the answer (in the financial industry, what are the commonly used Excel analysis techniques? - Microsoft Excel) mentioned in the construction of all kinds of models of the heart, in fact, the core point is to continue to practice plus constantly thinking about how to be lazy. Everything in the world, many are touching. The basic idea of doing Financial Model is actually very similar to programming, such as the famous and old-fashioned MVC: the logic of the entire Financial Model is divided into three layers, Model (responsible for the data), View (responsible for the presentation) and Controller (responsible for the business logic), ideally one of the layer of changes will not affect the other layer. These basic ideas gradually formed in practice or the impetus for laziness: high flexibility, the need to have a flexible framework to quickly meet the boss and the changing needs of customers Reusability, the project made Financial Model, can be put into the next irrelevant project to use Robust, minimize the frequent manual input or operation, the original data is concentrated in a module, change a data, the relevant data will be used in the project. The original data is centralized in a module, and if you change a data, the related data and module will be changed automatically. When I made most of the Financial Model, I basically followed the MVC framework to ask myself. The process of building a financial model is like building a high-rise building and adding modules layer by layer. The constant/core data/assumption data part includes: business constants (exchange rate and tax rate, etc.), historical data (past financial reports and market size historical data), forecast data that is considered reliable and cannot be altered, and core assumptions (such as assuming that the macro-economy grows according to 6-7%), etc. These data are roughly equivalent to the C html code, which is the same as the C html code. These data are slightly equivalent to the C language part of the h file, move a hair and move the whole body, so to be treated separately. As with the program, Excel's function can not appear hard-code numbers, so if a financial model in the "= 2 * 3.14 * r", basically can play back to redo. Scenario scenarios, including: the model needs to often adjust the important input parameters (eg: market penetration, Exit PE ratio, etc.). These parameters are best stripped out into a separate interface, which can be more easily controlled and adjusted to prepare for the Sensitivity Analysis later, and may even encounter a situation similar to seeking the optimal value with the gradient descent method mentioned above. Base Model. The core of this step is to make a forecast of the three financial statements, the most painful is the leveling. It is possible to use all kinds of complex functions (Indirect/Offset/VLookup, etc.) to perform the leveling without error and with very high reusability. Advanced Modeling. Based on historical and forecasted three statements, do some more complex financial analysis or valuation forecasts, including: DCF, Comparable, sensitivity analysis, etc. Presentation. Put out the outputs that users (including bosses or customers) are most concerned about, and present them in the most user-friendly interface. Of course, to do more extreme, you can adjust the Scenario and the important parameters of the interface is also put out to facilitate the user Manipulate Data (in fact, translated into Chinese more interesting: obscene data) in order to get the most satisfactory results. The following figure is a Financial Model that has struggled, basically covering the above logic and construction process, for your reference.