---
title: "Skill GET丨Excel is a magic tool when used well, but just a spreadsheet when not"
description: "Excel is the most mysterious but also the most efficiency-boosting tool among the Office trio, yet many of us treat it merely as a spreadsheet tool. Learn these tips and tricks to surpass 90% of your colleagues in Excel proficiency."
author: "New Distribution"
publisher: "New Distribution"
email: "zhaobo258@gmail.com"
telephone: "+8615854817671"
published: "2016-07-19"
language: "en"
canonical: "https://xinjignxiao.com/en/articles/skill-getexcel-is-a-magic-tool-when-used-well-but-just-a-spreadsheet-whe-a859fdc9/"
markdown: "https://xinjignxiao.com/en/articles/skill-getexcel-is-a-magic-tool-when-used-well-but-just-a-spreadsheet-whe-a859fdc9.md"
original_source: "https://mp.weixin.qq.com/s/aJEkm_mrXmxd89NCo6CDmA"
translation: "https://xinjignxiao.com/zh/articles/%E6%8A%80%E8%83%BDget%E4%B8%A8excel%E7%94%A8%E5%BE%97%E5%A5%BD%E9%82%A3%E6%98%AF%E7%A5%9E%E5%99%A8-%E7%94%A8%E4%B8%8D%E5%A5%BD%E5%B0%B1%E5%8F%AA%E6%98%AF%E4%B8%AA%E7%94%B5%E5%AD%90%E8%A1%A8%E6%A0%BC-a859fdc9.md"
attribution: "New Distribution — https://xinjignxiao.com/en/articles/skill-getexcel-is-a-magic-tool-when-used-well-but-just-a-spreadsheet-whe-a859fdc9/"
usage_policy: "https://xinjignxiao.com/ai-policy.txt"
---

# Skill GET丨Excel is a magic tool when used well, but just a spreadsheet when not

> Excel is the most mysterious but also the most efficiency-boosting tool among the Office trio, yet many of us treat it merely as a spreadsheet tool. Learn these tips and tricks to surpass 90% of your colleagues in Excel proficiency.

**Excel is the most mysterious but also the most efficiency-boosting tool among the Office trio, yet many of us treat it merely as a spreadsheet tool.**
**Learn these tips and tricks with us today, and your Excel skills will surpass 90% of your colleagues in no time!**
Still treating Excel as just a tool for drawing tables?
Too young, too simple.
Take a look at the image below.
It shows a 24-hour population activity map of Manhattan, New York.
Analytics firm DarkHorse Analytics obtained data from the U.S. Bureau of Labor Statistics and created this breathing map that shows the working and residential population of Manhattan over 24 hours.
Using conditional formatting and small squares, the map is visualized in Excel. Each frame of the GIF is a screenshot of an Excel sheet.
What? You say that's the work of professional designers and you don't need it?
Then these tips below are definitely useful for you.
1**10 Shortcuts**
**1. Quick sum? Use "Alt + ="**
Excel's function capabilities are powerful, and summing is one of the most commonly used functions. Simply press the shortcut keys "Alt" and "=" to sum a column of numbers.
**2. Quickly select non-contiguous cells**
Press the combination "Shift+F8" to activate "Add to Selection" mode. The status bar at the bottom of the worksheet will show "Add to Selection". Then click on non-contiguous cells or ranges to select them without holding down the Ctrl key.
**3. Change number format**
Excel shortcuts are not random; they follow a certain logic.
For example, if you want to quickly change the number format, <Ctrl+Shift+4> instantly adds a dollar sign to the number, because the $ symbol and the number 4 share the same key.
Similarly, "Ctrl+Shift+5" quickly changes numbers to percentage (%) format.
**4. Show all formulas with one key: "CTRL + `"**
When you try to check for errors in your data, <CTRL + `> reveals the formulas behind the numbers with one key.
The "`" key is to the left of the number 1 key :)
**5. Double-click to quickly apply a function**
No need to type the same function one by one. Once you've set up the function in the first cell, just move the cursor to the bottom-right corner of the cell until it turns into a small plus sign, then double-click, and the formula will be applied to all remaining cells in that column.
Isn't that much easier than dragging with the mouse?
**6. Quickly insert or delete a column**
For those who deal with Excel all day, this trick might save you some time.
When you want to quickly insert a column, type Ctrl + Shift + '=' (Shift + '=' is actually the + sign) to insert a column to the left of the selected column, and Ctrl + '-' (minus) to delete the selected column.
**7. Quickly adjust column width**
Want Excel to automatically adjust column width based on your text? Just move the mouse to the right edge of the column header and double-click, and it's done!
**8. Double-click the Format Painter**
The Format Painter is a great tool. But did you know that double-clicking it allows you to apply the same format to multiple cells?
**9. Quickly switch between worksheets**
Switching between worksheets doesn't mean your hands have to leave the keyboard (imagine if you learn these cool shortcuts, you won't need to touch the mouse at all).
"Ctrl + PgDn" switches to the worksheet on the right, and conversely, "Ctrl + PgUp" switches back to the left.
Well, Mac users seem to be out of luck with this shortcut...
**10. Use F4 to lock cells**
When filling data based on formulas in Excel, sometimes you want the referenced cells to change as you drag down, and sometimes you don't. When you need "absolute reference"—that is, locking cells—you must add $ signs before the row and column. Manually typing those dollar signs? That's crazy...
Actually, there's a simple trick: after selecting a cell, press F4 to input the dollar sign and lock; if you continue pressing F4, it cycles through: lock cell, lock number, lock uppercase letter, unlock.
If you think the above is too basic, then the following functions are for advanced users!
2**10 Formulas to Double Your Efficiency**
Functions (formulas) are what set Excel apart from other software, yet many of us use Excel as a plain table tool.
**1. NETWORKDAYS and WORKDAY functions**
If your boss asks you how many working days are left until New Year's Day, would you really count day by day on a calendar?
Too young.
The NETWORKDAYS function in Excel returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified as holidays.
You can use NETWORKDAYS to calculate an employee's accrued pay based on the number of days worked in a specific period.
And if your boss suddenly starts thinking about the future and asks you, "If you work 1000 more days from now, what date will it be?"
Don't cry.
Having such a boss is not your fault. Show him this GIF:
Yes, this function often returns a number. It's not that Excel made a mistake; it's your number format. Press Ctrl+Shift+3 to display it as a date!
**2. IF function**
The IF function means "if". If a condition is met, return one value; if not, return another.
The syntax of IF is:
> IF(logical_test, value_if_true, [value_if_false])
Here, logical_test is the condition to test. value_if_true is the value you want returned if the test result is TRUE.
For example, in the GDP table below, I labeled countries as "poor" or "rich" based on whether their value is above 5 million.
**3. SUMIF function**
Sum means "add up", and with "IF", it sums values in a range that meet specified criteria. For example, if you need to sum values greater than 1,000,000 in a column of numbers, use the following formula:
**4. Trim() function, Virgin Mary's blessing 1**
This function easily removes spaces from cell contents. For example, =trim(A1) will display only the non-space content if A1 has spaces.
**5. Clean() function, Virgin Mary's blessing 2**
The Clean function removes non-printable characters from text. When data is imported from other applications or formats into Excel, there are often strange garbled characters. Now, with the magical Clean function, your data becomes clean and tidy!
For example, after applying this function, inexplicable extra spaces disappear.
**6. SUBTOTAL function**
The SUBTOTAL function is actually a group of functions that returns a subtotal in a list or database.
SUBTOTAL is an all-rounder: it can average, count, find max/min, multiply, standard deviation, sum, and variance.
It can calculate anything you want!
**7. SUMPRODUCT function**
This function multiplies corresponding elements in given arrays and returns the sum of those products.
The syntax is: SUMPRODUCT(array1, [array2], [array3], ...) where Array1 is required, and its corresponding elements are multiplied and summed. Array2, array3... are optional.
For example, the function below multiplies corresponding numbers in two groups and adds them up.
**8. TEXT function**
The TEXT function converts a value to text in a specified format.
TEXT can also use special format strings to specify display formats. It's useful when you want to display numbers in a more readable format or combine numbers with text or symbols.
For example, suppose column C contains a set of numbers. To format these numbers as dollar amounts with two decimal places, use:
=TEXT(C1,"$0.00") & " per hour"
The syntax is: =text(value, format_text). Value is the numeric value. Format_text is the text format you want from the cell format settings.
**9. SMALL & LARGE functions**
The SMALL function finds the nth smallest value in a data set. For example, SMALL(B2:B20,3) finds the 3rd smallest number in the range B2 to B20.
Similarly, the LARGE function finds the largest values.
See what the GDP of the third lowest country in the list below is?
**10. INDEX+MATCH functions**
**INDEX+MATCH is a powerful combination in Excel; many people even consider proficiency in these functions as a standard for judging Excel mastery!**
These two functions are a bit complex. Grab a seat, and let's explain slowly.
index(r,n) is an index function that returns the value of the nth cell in range r.
And match(a,r,t) is a match function: when t=0, it returns the position of the cell in range r that exactly matches value a; when t=1, it returns the position of the cell closest to a (Chinese characters are usually compared by pinyin, numbers by value, and numeric symbols by place value).
Usually, you can combine the two functions for cross-table lookups: use MATCH to find the position of the matching cell, then use that position with INDEX to find the associated value.
Still don't get it?
No problem.
Now, LinkedIn editor got a set of part specifications (don't ask what the parts are for; I'm just a worker, and I'm working hard for you today).
......
This table is quite long, probably... 100 rows. That is, the specifications on the left correspond to the weights on the right.
Now, I have a bunch of these parts. After much effort, I've figured out all the specifications and listed them below.
Suddenly, the boss walks over:
"Editor, yes you! Look up the table and mark the weight for each part!"
......
Over 100 entries, and you want me to fill them one by one! What if there were over 1000!?
LinkedIn editor wants to slam the table, why should I do this manual labor!
But I held back.
Because I had already seen through everything.
I had a trick up my sleeve: Index+Match was just the right tool!
By looking up the specifications in the previous set of numbers, I quickly got the corresponding weights.
Let's see how it's done.
> **=INDEX($B$2:$B$100,MATCH(D2,$A$2:$A$100,0))**
>
>
>
>
> In this formula, MATCH(D2,$A$2:$A$56,0) means to find the row in the range A2 to A100 that "exactly matches" D2 (your part's specification). "0" means exact match.
>
>
>
>
> After finding that row number, the INDEX function finds that row in the range B2 to B100 and returns the corresponding weight.
>
>
>
>
> So your part's weight is revealed!
>
>
>
>
> The "$" signs are added to make the range an "absolute reference", so no matter how I drag down, the range stays fixed.
The boss was satisfied, and I was relieved.
Still don't get it?
No problem, copy this and learn slowly:
https://support.office.com/zh-cn/article/使用-VVLOOKUP、INDEX-或-MATCH-查找值-7144ef3f-e322-4f95-9e96-f1d743270438
3**Other Amazing Features**
**1. Did you know? Excel can insert screenshots!**
Many software programs offer screenshot plugins, like QQ and WeChat have shortcuts. But sometimes when editing documents in Office, you need to take a screenshot, and this trick is very convenient.
In Office (including Word, Excel, PPT), under the Insert tab, there is a "Screenshot" option. Below these words is a small triangle. Click it to see a "Available Windows" list. Click "Screen Clipping", wait 2 seconds for the screen to blur, and a crosshair cursor appears. Select the area to capture.
Or directly click a window in the Available Windows list (there are two windows in the image below) to take a full-screen screenshot.
**2. Conditional formatting makes your data visual**
Conditional formatting refers to formats that Excel automatically applies to cells when a specified condition is true, such as cell shading or font color, or adding a traffic light icon.
The biggest benefit of conditional formatting is that it highlights the data you want to find. Combined with formulas, it becomes even more powerful.
For example, I want to highlight the GDP of various countries in a significant format in the numbers below.
You can do it like this:
Or like this:
And like this:
Pretty cool, right?
Conditional formatting can also be used with the functions above, presenting a certain format when a condition is met. All these techniques can be integrated.
**Why do others always work faster than you? Now you understand. If you also learn these tricks, your Excel skills will surpass 90% of your colleagues. ▲**
## **Advanced: How powerful is Excel?**
Many senior programmers look down on VBA. Because programmers have a chain of contempt: Assembly > C > C++ > Python > Java, C#, and PHP (these three fight each other) > VB > HTML. In this long chain, VBA doesn't even have a place.
However, Excel+VBA is Turing complete, so most of the functions that programmers use to show off with various sexy languages can be implemented with Excel+VBA, often in a more efficient and faster way. Here we don't discuss efficiency and elegance.
Moreover, considering that most ordinary people don't have a programming environment (various dependencies, packages, OS, compilation environments, and IDEs), using VBA only requires opening the pre-installed Office and pressing Alt+F11 to enter the programming and execution environment.
You can even solve programming problems by recording macros, which is even simpler. You just need to modify simple code and execute it.
Considering the perfect integration of VBA with Office software, VBA is unmatched in convenience.
Finally, Office+VBA has strong shareability and portability. Any tested program can be easily executed on another machine; while other programs, even the simplest "Hello World", may not.
Therefore, this article discusses various cool features that can be implemented with Excel+VBA (and also extends to Office+VBA), mainly **for professionals in Professional Services and various industries who don't write programs but heavily rely on Office**.
A friend once told me, "Excel doesn't need programming. An Excel expert like me can solve all problems with functions and custom functions."
To such comments, I recall an experience from elementary school. Because I couldn't understand the value of imaginary numbers i (i^2 = -1), I asked my dad, "What's the use of i?" My dad said, "When you grow up and encounter more problems, you'll know the value of i."
**Author: He Mingke, LinkedIn columnist.**
**1. Automatic printing**
**Newbies in the workplace, unless their dad is Li Gang, have basically been "shadow emperors" (shadow = print, various copying and printing manual labor).
Especially in consulting and investment banking services, before a big client presentation or a big bluff meeting, spending hours or all night printing multiple documents is not a fantasy. And this task is repeated frequently with the same set of constantly changing files.
My first project at BCG was helping a large enterprise design and implement a KPI system from top to bottom. This involved dozens of departments and about 100 KPI tables, distributed across various Excel files.
Our task as 4 consultants:
> **·** Set up the basic format of KPIs, then each consultant was responsible for several departments, constantly modifying KPI tables in Excel, printing them out to discuss and revise with the relevant people and their leaders.
>
>
>
>
> **·** Each week, collect all KPI tables from all Excel files, print them in order by department, and make multiple copies to report progress to the HR head in charge of the project.
There was a time-consuming step: each week, we had to find the target worksheets in multiple Excel files, select the appropriate areas as output tables, and print over 100 tables in a certain format and order.
Previously, we did it manually. Each week, an Analyst would collect all the latest Excel files, open each one, select the appropriate worksheet, set the area and format, and print. Each time took almost one to two hours, and errors were not guaranteed.
So I wrote my first VBA program, which was basically modified from a macro recording, without using reference books or search engines, relying solely on F1 and auto-suggestions. I'm posting it as a special memento. The function was to automate the above manual process. Press a button, and wait for the printer to output in order.
Later, the upgraded version of this program scheduled multiple printers to further improve efficiency and minimize the probability of queue errors caused by paper jams.
**2. Creating charts and GIF animations**
**Chart creation is a must-have task for every Office user. Making good tables is definitely a tool for promotion and salary increase, as well as for slacking off. Use Excel+VBA to create several cool information maps, using VBA to color each province's shape.
(The coloring part comes from an online program; the GIF animation was made by myself.)
To further enhance the coolness, I also used VBA to turn these continuously changing charts into GIF animations, but Zhihu doesn't support GIF display.
**3. Creating complex analytical charts**
The figure below studies the mutual conversion relationships between users of various car models. To turn a one-dimensional conversion rate vector into a two-dimensional matrix, I used the following complex formula:
=IF(ISERROR(OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",COLUMN(A4)),$D$3:$D$600,FALSE),0)/OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",ROW(A4)),$D$3:$D$600,FALSE),0)),
"",OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",COLUMN(A4)),$D$3:$D$600,FALSE),0)/OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",ROW(A4)),$D$3:$D$600,FALSE),0))
Also, to use color shades to represent the magnitude of conversion rates for easy comparison, I used VBA to color the matrix below. Some might say conditional formatting could be used, but VBA maintains the highest flexibility and efficiency.
**4. Generating standard Word files based on formatted information**
This was a project I helped a friend with. Their lab studies certain types of accidents and identifies major accidents, finally publishing official reports in Word.
The previous workflow was to complete calculations and simulations in professional software, then fill in key information according to the formal report's eight-part essay style, and finally write a Word file.
The report writing process was boring and low-tech, but it had to be repeated.
Through the Word+VBA shown below, I completed the main interactive interface and connected to the calculation software.
After obtaining key information through simple interactions, the calculations were done in the background, and the key information was filled into the eight-part essay Word template, finally completing the report while storing structured information in an Access database.
**5. Managing distributed task flows through Excel and outputting Excel tables to PowerPoint**
**An international large automobile manufacturer was about to launch a new brand and its new model, facing the dilemma of a model gap. The launch of this new model was critical and could not miss the time node.
However, the launch involved countless branches: manufacturing, product, market, channel, marketing, PR, finance, etc., while also coordinating two European headquarters and two Chinese branches.
The core task of this consulting project was project management, controlling the overall progress of the large project, and reporting progress to the China CEO weekly, identifying key nodes prone to problems to allocate resources.
We 4 consultants were assigned to several departments or project branches, working with the team to plan processes, draw Gantt charts, confirm milestones and time points, assign responsible persons, etc.
When we returned to the office each day and aggregated progress, we discovered challenges and difficulties: each task line did not develop independently; they were intertwined and influenced each other.
> **·** Some core personnel appeared in multiple task lines. For example, the finance person responsible for budget appeared in almost all lines for budget approval.
>
>
>
>
> **·** Some task lines' milestones were prerequisites for other task lines' milestones. For example, the new car's launch time affected the press conference time, and the passing of relevant regulatory tests affected the car's launch time, etc.
When the number of task lines increased and the intersections became more frequent, the aggregation task increased geometrically. This was the problem we encountered during the project.
So I used Excel+VBA to automate this work. The main functions implemented:
> **·** Automatically consolidate the scattered Excel files from the 4 consultants into one large summary table, as shown below.
>
>
>
>
> **·** The tables in each consultant's hands were divided by department. After consolidation, different types of tables needed to be output according to different dimensions.
>
>
>
>
> For example: output tables by task line, by responsible person, all delayed tasks, all tasks requiring key resource investment, etc.
On this basis, I also had to paste all the tables under various dimensions (about 200+) into PPT in the required format, submitted to the China headquarters weekly for reporting and evaluation. The dense tables are shown below.
So I wrote another program to output Excel tables to PowerPoint, simplifying a task that took a secretary hours each time into a one-click release, and allowing updates to the PPT from Excel.
The program for this project was quite large, almost like writing a mini Microsoft Project for project management.
Finally, the dense PPT below needed to be updated weekly, with nearly 100 slides each time, but it was basically done automatically through Excel.
Because the PPT template didn't change much each time, I recorded these templates. When updating weekly, I only needed to change the data in the PPT based on the latest data in Excel.
**6. Reverse engineering assumptions from results**
**A typical financial model calculates final results based on multiple assumptions. But while serving a top mobile phone brand, we encountered the awkwardness of reverse engineering.
Originally, we were to calculate the number of salespeople needed based on a certain service level of field sales personnel; but during the project, the headquarters had already determined the headcount for salespeople, and instead asked us to determine the service level based on the HC.
However, the service level is not a single variable; it is determined by multiple factors such as retail store coverage, sales visit frequency, and service depth during visits, and it can also vary from first-tier to wireless cities.
So I had to resort to the Excel+VBA magic again.
First, build the financial model based on conventional thinking to get preliminary HC results.
Then write a VBA program to adjust multiple factors like retail store coverage, sales visit frequency, and service depth according to different scenarios, priorities, and weights, while setting acceptable ranges for these major factors, gradually approaching the preset HC value.
Without a program, this was previously done manually by adjusting to match results, and because of different scenarios, multiple adjustments were needed.
With the program, it's basically automatic, and it can intelligently set priorities and weights without manual intervention.
**7. Mass downloading Bloomberg data and completing analysis**
**Through Bloomberg's VBA API, I mass-downloaded tick data and order books for hundreds of target stocks.
Then, based on pre-built mathematical models, I completed calculations in the background, converting the real-time data into real-time trading costs for each stock, displayed in the Excel interface most commonly used by traders, to help them evaluate current trading costs and optimize trading strategies.
**8. Conclusion**
**Calculating, I spent three years as a consultant at BCG, writing tens of thousands of lines of VBA code (all manually typed, no copy-paste or auto-generation), with each project ranging from one to several thousand lines.
In the end, I used Excel as a central control interface, similar to EMACS, where I could control the company's printers, Word, PowerPoint, etc., automatically complete various tasks, and update and fetch data.
Because Excel's data is more structured, it has an advantage as a central control platform over Word and PowerPoint.
-END-
The best FMCG distributor learning platform in China
Focusing on providing professional, practical, and actionable tutorials for enterprises and distributors
Committed to helping Chinese FMCG distributors grow rapidly
**The most professional and practical knowledge base in the FMCG industry**
Reply with the red number below to get the corresponding content
Reply with number 1 to view the complete knowledge base
| **001** Excellent article selection | **002** Distributor market operations | **003** Terminal visit management | **004** Sales supervisor skills | **005** Sales improvement techniques | **006** Channel expansion | **007** Managing distributors | **008** Distributor development | **009** Distributor internal operations management | **010** Team management | **011** Efficient distribution techniques | **012** Sales manager's 18 skills | **013** KA operation methods and strategies | **014** First lesson for new salespeople | **015** Internet, brands | **016** Distributor B2B transformation |
[Long press QR code to follow]


---

## Copyright and AI use

This article is sourced from New Distribution. Search, quotation, summarization, and model training are permitted, but every use must credit New Distribution and retain the canonical source URL.

Contact: zhaobo258@gmail.com · +86 158 5481 7671