In English, 'Excel' means 'to be superior or skilled at something.' As software, Microsoft's Excel spreadsheet program is a powerful tool for many 'table brothers' and 'table sisters.' What hidden skills in Excel make you wish you'd known them sooner, so much so that you'd immediately offer your knees? Come and learn with the editor to boost your knowledge. I. Layout Tips

  • Before sending an Excel file to others, try to position the cursor where you want them to look first, such as the Home position (A1), or the conclusion sheet. For long tables, position to the very top.
  • Freeze the top row when necessary; hide content that is not necessary but could be questioned.
  • Bold row and column headers, and appropriately adjust text color and fill color—beneficial for both you and others.
  • For smaller tables, place them in the top-left corner, but leave column A and row 1 empty, and add appropriate borders to the table for a better look.
  • For similar data, try to keep row height, column width, font, and font size consistent. Don't drive perfectionists crazy!
  • Define standard formats, such as decimal places for percentages, sufficient column width for phone numbers, and localized time display.
  • Don't use fonts that aren't available on other computers unless the spreadsheet is only used on this one computer. II. Operation Tips
  • Alt+Enter to wrap text within a cell.
  • Ctrl+Shift+Up/Down to select all data in a column; add Left/Right to select multiple columns.
  • Ctrl+Up/Down to jump to the top/bottom of the table.
  • Ctrl+C/V not only copies content but also formats and formulas.
  • Ctrl+D/R to copy data from the cell above/left.
  • There's also a handy cell format converter; I recommend getting familiar with it.
  • Ctrl+F/H for find and replace; click 'Options' to replace specific formats, etc. It's a whole new world!
  • F4—yes, you read that right, F4! It repeats the last action, such as inserting rows, setting formats, etc. F4 is incredibly useful for frequent operations!
  • ' (the apostrophe) For example, when entering a URL, it usually becomes a hyperlink automatically. Entering ' before the URL solves that.
  • Copy, Paste Special has some very useful options—Values Only, Transpose (I personally recommend the TRANSPOSE formula).
  • In formulas, to switch absolute references, directly select the target and press F4 to cycle through options like A1, $A$1, $A1, A$1. III. Formula Tips
  • IF, COUNTIF, SUMIF, COUNTIFS, SUMIFS—learn these together for conditional counting and summing.
  • MAX, MIN, LARGE—use these together for simple data analysis.
  • RAND, RANDBETWEEN—use these together to generate random numbers, or even random passwords (combine RAND with CHAR to generate random uppercase/lowercase letters and numbers).
  • Text functions: MID, SEARCH, LEN, LEFT, RIGHT—learn them together; simple but extremely practical.
  • For rounding, I prefer the ROUND function. For example, with a column of data like 2.04, 2.03, and their sum, if you display one decimal place, you'll see 2.0, 2.0, but the sum shows 4.1, which can be confusing when printed.
  • SUBTOTAL: for summarizing and analyzing filtered data.
  • SUMPRODUCT: returns the sum of products of corresponding ranges; no need to multiply A1*B1, drag down, and then sum.
  • VLOOKUP function—no need to say much; it's a lifesaver. Also recommend the LOOKUP function: LOOKUP(1,0/(condition), lookup array or range).
  • OFFSET function, often used with other functions. For example, to replicate each row of a 10x20 table three times in order to make 30 rows: =OFFSET($A$1,INT((ROW(A1)-1)/3),COLUMN(A1)-1,1,1) and drag down. Since columns aren't used, it's equivalent to =OFFSET($A$1,INT((ROW(A1)-1)/3),0). I used to note it like this: =(A1, offset down (floor((row-1)/3)), offset right 0).
  • TEXT, e.g., 19880110 -> TEXT(A1,"0-00-00") converts to 1988-01-10. Many uses.
  • WEEKDAY: to convert dates to 'Week X' for schedules.
  • COLUMN(target cell): returns the column number of the target cell; sometimes very useful.
  • TRANSPOSE(target range): magical transposition, turning rows into columns and columns into rows.
  • &: to append certain characters after the target cell; occasionally useful.
  • Arrays: though complex, some formulas with arrays are incredibly powerful.
  • Use Baidu often. For example, I once had a problem converting 'X minutes Y seconds' to seconds, like 172分52秒. After searching Baidu, I got this formula: =IF( IFERROR( FIND( "分", $E2 ), 0) > 0, LEFT( $E2, FIND( "分", $E2 ) - 1 ) * 60 + IFERROR( MID( $E2, FIND( "分",$E2 ) + 1, FIND( "秒", $E2 ) - FIND( "分", $E2 ) - 1 ), 0 ), LEFT( $E2, FIND( "秒", $E2 ) - 1 ) * 1 ). Baidu is very powerful. IV. Chart Tips
  • Use different charts for different scenarios. Here's a very insightful chart.
  • PivotTables and PivotCharts—yes, recommended by many.
  • Chart Design—Layout: flexibly use 'Secondary Axis' when dealing with multiple data types.
  • Select Data—Right-click—Change Chart Type: flexibly combine bar charts and line charts on one chart. V. Technique Tips
  • Data—Text to Columns: split data in a column into multiple columns, e.g., 'XXX Province XXX City' into province and city columns, 'XX hours XX minutes' into hours and minutes. You can split by width, text, delimiters, etc. This is used in many scenarios; learn it first.
  • If you don't rely on Excel for a living, you don't need to be a geek. Instead, learn Excel's logic—combine simple formulas, sorting, replace, IF, and other global operations to get results. You don't always need a long formula and drag down. For example: How to reverse a column of irregular data? Create a column, label it 1, 2, 3..., drag down. Sort by this column as the primary key, change ascending to descending, and extend the target column. After that, you can delete the helper column. How to insert a blank row after each row of data in a target range? Create a column, label it 1, 2, 3..., drag down. Below the blank rows, label them 1.5, 2.5, 3.5..., drag down. Similarly, sort. Conditional Formatting—Highlight Cells Rules—'Duplicate Values' is very useful for real-time entry and checking.
  • If conditions allow, upgrade to Office 2013. Excel 2013 is much better than 2010! For example, new SUMIFS, AVERAGEIFS, etc. Also, when you select a region, the 'Quick Analysis' button in the bottom-right corner automatically generates data bars, color scales, column charts, summary charts, pivot tables, line charts, etc. It's amazing! VI. Add-ins Power Map Online maps + online presentation + video creation. Here's a random heat map of China's wealthy: And a column chart. Power View Charts with interactive visualization, great for presentations. The default color scheme is nice, and when presenting, clicking will display the corresponding data based on the type you click (like the stacked column chart above). Power Query This has many uses; I mainly use it for two things:
  1. Online web scraping: You can crawl and analyze simple data without learning a programming language. For example, enter the URL of Sina Stocks, and it automatically grabs multiple tables. I opened one: 2. Connecting to databases: You can query and analyze data in databases without learning SQL syntax. This isn't convenient to screenshot. For example, common SQL queries like select * from ... where xxx = xxx and xxx>xxx group by xxx can be done in Power Query by clicking and filtering. Various Excel Toolboxes I won't introduce these much; they're not commonly used, but they suit some professionals who use Excel long-term. SmartArt A lifesaver. I no longer need to create graphics in AI or PPT and paste them over. VII. Other Tips
  • It's okay if you don't know how to write macros; learn to use others' macros (search Baidu for 'Excel macro collection'), how to save as .xlsm, and how to record macros.
  • If you have time, visit Excel forums like ExcelHome; they're full of experts.
  • I highly recommend videos for beginners, intermediate users, and 'intermediates': Wenku courses. Even the first few videos, though the titles seem simple, contain many operational highlights that self-taught users often miss.
  • If Excel isn't enough and you know programming, and want to show off, go to Baidu for SPSS. Excel is a huge and complete 'system,' and its uses vary across industries. So answers will differ from person to person. I'm just sharing my own work experience to spark discussion. Reply with the following keywords to categorize and read related professional articles: Sales Supervisor, Second-tier Management, Regional Manager, Distributor Management, New Channels, City Manager, Competition, 2015, Manufacturer-Dealer Game, Product Stagnation, Terminal Visit Management, Route Management, Deep Distribution, Internal Management, Sales Skills, Profit Improvement, Recruitment, Distribution, Daily Management, Team Motivation, Trade Promotion, Sales Mistakes, New Product Launch, Township Market, New Product Pricing, Sales Target Achievement, Closing, Market Visit Inspection, Baijiu, Beer, Sales Increase, Agency Products, Channel Crossing, KA, Terminal Merchandising, New Market, Market Operation, Learning, Book Recommendations, Inventory Management, New Salespeople, Consumer Promotion, Execution, Old Products, Expired Product Handling, Model Market, Investment Promotion, New Media, Distributor Development, Performance Appraisal, Assessment, Annual Planning, Shopping Guide, Morning Meeting, Display, Transformation, Inventory Pressure, Holidays, Distributor Cost Control, Channel Operation, Marketing Theory and Laws, Brand Truth, Order Meeting, Team Management, Training, Debriefing, Debriefing Report.