Introduction: every day forced to work overtime by statements, statistics? It's a simple aggregation, but you don't understand, excel. In fact, excel is not a book, it's the most efficient god in the world of work -- it's easy to get 80% of the office data needs by touching the door. Today's super-detailed guide, from basic operations to pit avoidance techniques, the full-blown speech, and so can the layman
First of all, understand: what's excel's skeleton
Don't get scared by a full-screen grid when you just open excel! Actually, it's so simple. It's like a notebook with a grid:
Start with 3 basic operations, less devious:
Selected cells: click on the grid; drag the mouse to select an area (e. G. A1 to c10); select the entire table by pressing ctrl+a; enter and save: the selected cells are directly typed and confirmed by enter; create a custom for ctrl+s to be saved at any time so as not to break free; copy paste: the selection is copied by ctrl+c and then pasted by ctrl+v, and if you want to keep the original format, use the right key "stamp-reserve source"。
Ii. Formatting technique: to change your data from "hot" to "high"
The same report. Why is it clear and professional? The key is "formatization." it takes five minutes to do these steps, data instantaneously
Iii. Effects: these techniques make you less than 1,000 keyboards
The easiest mistake for a rookie is to repeat the mechanical operation. Remember these shortcuts and techniques and double the efficiency:
1. Batch input of the same content (ctrl+enter)
If you want to enter the same content for 100 cells (e. G. "to be audited"), you don't have to knock: select all cells that need to be entered by typing directly to press ctrl+enter and fill them all at once
2. Serial fill: auto-generated serial number, date
Do you want to fill in a serial number of 1 to 100, or a continuous date? With the "fill handle" one key:
3. Screening and sorting: fast search for data (ctrl+shift+l)
Faced with hundreds of lines of data, you want to quickly find out "sales department employees" or "records paid more than 5000"? Use filtering: if you click ctrl+shift+l (or click "data" filter") on the header of the selected table, you can click on the arrow with a pull-down arrow, and you can filter the data according to the conditions (e. G. "text filter - equal to - sales department"), the data that you do not want to see will be automatically hidden; if you want to sort from high to low, click on the "pay" column to select "down" with the arrow。
4. Freezing windows: no longer used to roll back on screen
After the data exceeds 10 lines, the scroll header disappears? Freezing windows to save the scene:
Iv. A mandatory function: to make the data speak for itself
The function is at the core of excel, not to be afraid of formulae complexity, remember this 6 hf function, which solves 80% of office needs, with a full case description:
1. Sum (summation): fast count totals
Function: calculating the sum of values in a given area, ignoring text and empty values; formulae: = sum (data range); case: to calculate the sum of wages from a1 to a10, input = sum(a1:a10) and produce results by enter; step forward: multi-conditional demand and use of sumifs, e. G., calculation of the sum of "sales' salaries are greater than 5000", input = sumifs (a1:a10, b1:b10, "sales", a1:a10, "> 5000") (a column, column b)。
2. If (conditional determination): automatic classification
Function: return to different results according to conditions, such as rating of achievement and completion of tasks; formulae: = if (judgement of condition, content that meets condition, content that does not); case: determine whether b column scores pass (60 points), enter =if (b1>60, "failure", "failure",) and drag down the formula to automatically classify all achievements; step: multiconditions use ifrs, such as =ifs (b1> =90, "fine", "b1> = 80, "good", "b1>60, "failure", "failure", "failure", 1", "failure"。
3. Vlookup (vertical search): data matchor
Function: find data in keywords, e. G., pay based on employee name, inventory based on product name; formula: = vlookup (required content, search range, return column number, precise match); case: in a1:c10 (list a, column b, column c), find "stick three" wage, enter =vlookup ("stand iii", a1:c10, 3, false); note: last parameter must be written false (precision matching) to avoid misdata; the search should contain "key word column" and "result column" and the key word should be on the left。
4. Average (average): average oversimplified
Function: calculates the average of an area, automatically ignoring empty values and text; formulae: = average (data range); case: calculates the average b1 to b20 performance, enter = average (b1:b20), with instantaneous results。
5. Round (rounded): avoiding decimals
Function: to retain values by specified decimals, for example, 3. 14159 places; formula: = round (value to be processed, number of decimal places to be retained); case: input = round (3. 14159, 2), resulting in 3. 14; to use roundup upward, to take down to rounddown。
6. Datedif (date margin): calculation of length of service, convenience of days
Function: calculates the year, month and day margin between two dates (excel hides function, enters without hint, but can be used); formulae: =datedif (start date, end date, unit); case: calculates the employee's number of years of service (a1 is entry date, today is end date), enters =datedf (a1, today(), "y") ( "y" represents year, "m" represents month, "d" represents day)。
V. Guide to pit avoidance: 5 mined areas that are the easiest for newcomers to step on
A lot of people make mistakes with excel, not inoperable, but in the default setting of "holes". Remember these solutions, data double:
1. Lead zero disappears (e. G. 001 becomes 1)
Question: when entering the employee number 001, 002, excel automatically removes the lead zero, which leads to incomplete numbering; resolves: before entering, the cells are set as text format - select the top of the column to "start " numeric numbers " , group " text " , then enter to keep the lead zero; you can also click a single quotation (') in english and lose 001 when entering。
2. Long numbers become scientific algorithms (e. G. 123456789012 becomes 1. 23e+11)
Question: when entering numbers of telephone numbers, bank cards, etc. That exceed 11 bits, excel automatically displays them as scientific algorithms that fail to see the full number; solution: select column "starting "numbers" enter "0" in the type box to show it in its entirety; or set the text format earlier。
3. Several changes to id numbers 0
Question: 18 id numbers exceeding the excel 15-bit precision limit, entered into a scientific array, then 3-bits automatically turned to 0; solved: cells must be formatted in advance! Select the column where you want to lose your identity card to be "text" and enter it to keep the full 18-bit number。
4. Misformatted dates (e. G. 1/1/2025 and 2025/1/1)
Question: input date formats are not uniform, resulting in the inability to calculate the date margin (e. G., length of service, due date); resolution: selected date column "starts " number" group "date", selects "date" and selects a uniform format (e. G., "2025/01/01"); if a confusing date has been entered, use the "data-segregation" feature, last step "date" format, auto-standard。
5. Misformatted reproduction paste
Problem: copying data from somewhere else to excel, in a broken format that does not match the original table; solution: right-click selection "specific paste" when pasting select " values" (only data to be retained) or "forms" (only copy format) according to the requirement to avoid bringing in redundant formats。
Advanced functionality: data lens table, 5 minutes for complex statistics
If a large sum of data is required (e. G., gross wages by sector, sales by month), the use of a data permeation table saves more time, which is extremely simple:
Select all data (including headers)
Click at the top to create a new sheet by default on inserting zoom data perception table and click " ok "
Right field list, drag the "sector" to the "line" area, "wage" to the "value" area, generate a summary of the wages of the various sectors in an instant
If you want to switch to statistics (e. G. Average wages), double-click the "value" area for "sum: wages" and "means."。
The data lens table does not have to write formulas, drags fields to change statistical dimensions, and recruits can quickly make professional reports
Finally, excel study small advice
Excel will be able to meet 90% of the daily work needs without learning all the functions, first learning the basic operation, efficiency skills and six high frequency functions of the paper. You can try more when you start using it, you can cancel ctrl+z if you are wrong; you have a problem (e. G. What to do with a particular report), you can just search "excel do xx report steps" and you can remember it a few times。
Actually, at the heart of excel is freeing hands and giving it the job of repeating it, you can make time to do more important things. From today on, try to process the reports with these techniques, and i'm sure you'll find that the office could be so easy
