Common Function Formulas and Techniques

To learn common Excel functions, we don't need to remember the specific operations of each function, but we need to at least do the following two things:

Common names for function formulas: What are the commonly used function formulas in Excel, and most importantly, we need to remember the common names of function formulas. Why? Once we receive some commonly used function calculation requests, if we are not familiar with the specific formulas of the functions, but at least we need to know which keywords to search for. For example, if our Excel spreadsheet has a large number of characters and our boss requires us to sort by character, we can search for results on search engines using the keyword phrase "sort by number of characters".

Search skills ensure the accuracy and authority of search results: mastering keywords, the next ability we need to possess is keyword search and filtering the articles we need from a large number of search results.

Judging the accuracy of search results from domain names and website names: The results we search for based on keywords can be varied (many of the feedback information is not what we want or the search results do not match our actual needs), so we need to judge the authority of search results from domain names and website names. For example, when we search for Baidu Experience and some Excel/Office forums, the information here is often more authoritative and accurate. We can then judge whether the technical articles we want are based on the information provided by these websites (especially by reading the user comments at the end of the article, we can basically judge whether these function formulas are correct or not)

Here are some Excel function processing examples that we often encounter in our work. I will first list the keywords of these function companies, and then I will gradually list these Excel functions (be careful not to stick to rigid formulas, and flexibly apply formulas based on your own Excel spreadsheet data)

---Extract the date of birth from the ID card number
---Extract gender from ID card number
---Age determination from ID card number
---Calculated by ID number to today's age
---Age groups of men and women according to ID number
---Calculate age based on date of birth
---Calculate the zodiac sign based on the year and month of birth
---How to find a person's age at a specified date?
---Formula for calculating retirement age
---Seeking seniority
---Calculate length of service
---Age and length of service calculation
---Automatically display the current date formula
---How to automatically fill in the current date in a cell
---How to determine if a day is Sunday or not
---What day of the week is a certain date
---What function can display the current week
---Request the number of days in this month
---Calculate the number of days excluding Saturdays and Sundays in the current month using a formula
---Display yesterday's date
---Regarding Date Collection
---How to distinguish between the beginning, middle, and end of a date
---How to obtain the maximum number of days in a month
---Date format conversion formula
---Convert 198405 to 1984.05
---How to convert Elephant 22 into Day 22? Convert to the day of the current year and month
---Convert 'May 2006' to 'May 2006'
---Convert 'June 12, 1968' to '1968/6/12' format
---Convert "June 12, 1968" to "1968-6-12" format
---Convert the date format of 1993-12-28 to December 1993
---Convert the date of "1978-5-2" containing year, month, and day to the format of "197805" with only year, month, and day
---How to convert "99.08.15" format to "1999.08.15"
---To maintain the format of 2005/8/6
---Convert "December 25th, 2003" to "2003-12-25" format
---Date format conversion
---Date format conversion issue
---To automatically obtain the "Compilation Date: XXXX Year X Month X Day"
---Automatic sorting
---Sort by odd and even numbers
---Automatically generate serial numbers
---How to automatically indicate the size sorting of numbers in column A
---How to set up automatic sorting
---Duplicate data to obtain a unique ranking sequence
---Sort by character count
---Sort mixed content of letters and numbers
---Random sorting
---The problem of sorting
---How to make a sequence automatically add numbers
---A sorting problem
---Automatic sorting of numbers, unchanged after insertion
---Generate automatic numbering based on repeated name columns according to patterns
---Ranking function
---Automatic ranking formula
---Average score and total score ranking
---Seeking ranking
---Ranking
---General ranking based on scores
---For ordinary rankings with the same score, non duplicate rankings are performed in order
---Ranking based on high or low scores
---American style ranking
---Chinese style ranking
---Seeking the most concise automatic ranking formula
---Ranking after sorting
---Rank ranking
---Joint ranking based on double rank scores
---Ranking between two columns
---Equal ranking
---Unequal ranking (rows ranked first)
---Unequal ranking (ranked first by rank)
---Ranking in sequence
---There is a parallel ranking
---No tied ranking
---Parallel segmented ranking
---No parallel segmented ranking
---Score ranking
---How to rank
---Data ranking (ranked every few rows)
---Ranking backwards based on scores
---What is the reciprocal ranking function
---How to achieve daily ranking of workshop output
---Ranking based on the score of one subject when the scores are the same
---Automatically generate serial numbers after filtering and summarize them
---How to filter odd rows
---Function filtering name
---Ranking filtering
---How to achieve fast positioning (filter out non duplicate values)
---Common Function Formulas and Techniques
---How to list A1: L9 in column N
---The issue of automatically assigning gender codes
---How to delete * symbol in EXCEL
---Go to the space function
---How to remove characters and spaces from cells
---How to quickly remove spaces from different rows and columns in a table
---How to prohibit the input of spaces
---Replace strings in cells
---Convert the numbers in a cell into a specific character format
---Transform a column with over 600 cells into multiple columns on a page
---Transform N columns into M columns
---One column to four columns
---Four columns become one column
---Repeat filling four times
---Multiple rows of data arranged in a column
---Divide a cell into multiple columns
---Convert lowercase letters in cell numbers to uppercase letters
---Align names left and right
---The number is centered and the decimal point is aligned
---Format - Cells - Numbers - Customize -??? .??? -Confirm
---Format - Cells - Numbers - Customize -??? . 0? - Confirm
---Compare whether the contents of two cells are consistent
---How to set so that each cell in this column can only input 1
---How to make odd rows on a worksheet have a red background and even rows have a blue background
---Calculate the number of cells in a specific set of cells that meet the criteria

---Convert text formatted numbers into real numbers
---Set page numbers
---How to insert page numbers in Excel spreadsheets?
---How to set the footer homepage as page 5
---Footer issue with the table
---Unrestrained header
---Determine if the current line is greater than the line with the page break
---Get the page where the current row is located
---Get the total page
---Print header
---How to not display error value symbols in Excel printing
---Handling of some non printable characters
---That function can display the zero values before the single digit number
---If you want to insert 100 lines in front of A3
---May I ask how to paste a new line every 30 lines
---There are 10 consecutive rows of data in the worksheet, and now we need to interval each row by 2 rows
---How to add a blank line below each row in a large table
---Insert blank rows in Excel
---Quickly delete empty rows in a worksheet
---Quickly delete blank lines
---Delete a lot of blank lines in Excel at once
---How to achieve a page with a title every 30 lines
---How to add title items across lines
---How to remove a tab
---Remove default table lines (network cables)
---Box lines of the table
---The identification of the column label has changed
---The Meaning of Symbols
---Double click formatting brush can even copy text (not in text format)
---The wonderful combination of searching and formatting
---Cursor movement
---The last line of text
---The last line is a number
---How to quickly locate the last row of data in EXCEL
---How to quickly sum using SUN function
---Quickly view all worksheet formulas in Excel
---Set line spacing in Excel
---How to change the height of multiple rows simultaneously
---Quick Line Break
---Wrap the text
---Quickly convert rows and columns in Excel
---Invert the contents of the original column
---Quickly return to cell A1
---The clever use of the Enter key in copy and paste
---Select annotation cells once
---Insert annotations in all cells at once
---Insert annotations in the formula
---Fill discontinuous cells with the same data
---Filling in blank rows
---How to use functions to implement automatic filling downwards
---How to set automatic saving
---Avoid hyperlinks when entering website and email addresses
---An equal sign is automatically added in front of the cell
---Stamp with official seal
---The wonderful combination of searching and formatting
---The wonderful use of double clicking with a mouse in Excel
---Examples of Quick Localization Techniques in Excel
---Steps to Insert Flash Clock into Excel
---Convert lowercase numbers to uppercase Chinese yuan
---Easily create super complex Excel table headers
---Extract the character after a certain character in the cell
---Formula for character extraction
---How to determine the number after the * sign
---How to extract the number in the middle of a number
---How to extract the middle one from three numbers
---Formula for taking the last three digits of the numerical value
---Retrieval function
---How to extract numbers from cells (discontinuous in strings)
---How to extract discontinuous numbers in a string
---How to extract the characters before and after "-"
---How to delete: the following text
---How to only take the text after '.'
---Retrieve the string from the cell content
---How to extract several digits (characters) from a string of numbers
---How to extract numbers from a cell
---Split Text
---Truncate the number to the decimal point according to the given number of digits
---Cell number extraction problem
---Extract names using keywords
---How to remove the first few characters from text
---Remove the last character uniformly from a column of text
---How to remove the first number from a cell
---On the method of taking the last word
---How to remove the last digit of a cell
---How to add "p" before a column of already entered data
---What function can insert characters
---How to add an "*" symbol before data
---Add a number before the number
---How to split numbers
---Splitting data in cells
---Splitting of Cells
---How to split word groups
---Use the hyphen '&' to merge text
---How to merge content of different formats and positions into one cell
---Merge the contents of different cells into one cell
---Merge and Split
---Merge the contents of different cells
---Regarding the combination of text in tables
---Request for disassembly and merging formula
---How to separate letters and numbers
---Combine the scattered contents of each cell into one cell
---Merge and calculate cells from multiple worksheets
---Set the subtotal and total rows to different colors through conditional formatting
---How to implement such a conditional format
---How to set different font colors across lines
---Display different types of data in different colors
---How to create such an EXCEL spreadsheet (color crossing)
---Conditional format
---Set color stripes using conditional formatting
---Tips for Same Data
---How to display two decimal places for values less than 10 and one decimal place for values greater than 10
---How to add "+" and "-" to a numerical value based on its positive or negative sign
---Customize Cell Format
---Convert all the numbers in the cell to 10000 yuan representation
---Is there a way to convert data in a table from units of yuan to tens of thousands of yuan
---Common custom formats
---Custom Format
---How to define format
---Set separation lines between tool buttons
---Customize the area for the title of each page
---Format issue within a single cell
---Customize the display format of cell numbers
---Cleverly use location selection criteria cells
---Can the font and size of the labels on the worksheet be changed
---Cells A1, A2, and A3 of sheet 1 are linked to sheets 2, 3, and 4, respectively
---Experience and skills
---How to display different colors in separate rows
---Set the cell to have a "convex" or "concave" effect
---Design colored numbers in Excel
---The beauty of defining a name
---Zero value problem
---A tax adjustment formula
---The function of calculating prices
---Automatically calculate overdue fees for accounts receivable
---Elimination rate
---A function for applying housing provident fund
---How to convert numerical values to percentage format using formulas
---High score formula
---Automatic award rating
---How to sum data with units
---Dynamically sum column a
---Dynamic summation formula
---Sum of jumps in columns
---Regular cross row summation
---How to achieve odd or even row summation
---Sum of singular numbers
---Count the total value of even numbered cells
---Setting the formula for cross row summation
---Sum the same items in separate columns
---Add up in rows or columns
---May I ask how to perform inter row addition within a hundred rows
---How to add data from columns with a certain interval
---Sum of separated columns (columns A and B)
---Formula for Sum of Separate Columns
---Sum of separated columns
---Regarding the issue of summing up between rows and columns
---Calculate the sum of the products of the corresponding elements in two columns in EXCEL
---Calculate the sum of values between 900 and 1000
---Double condition summation
---How to achieve such conditional summation
---A1: How to sum A10 numbers when displayed in text format
---Seeking harmony
---Remove the two maximum and two minimum values, and then sum them up
---Remove the two highest and lowest scores, and display the removed scores
---Eternal Peace Seeking
---Sum by font color
---How to divide classes and count the number of men and women
---Number of cells with a statistical value greater than or equal to 80
---Calculate how many abc are in A1
---Conditional statistics
---How to count the number of different age groups
---How to calculate the number of people aged 20-50?
---How to count the number of people aged 40-50 .
---To count the number of 7-year-old girls
---Count the number of people
---How to count the number of people in A1: A10, D1: D10?
---How to make Excel automatically count from scratch to the current cell
---Request the number of people in each score range
---What is the method to count even numbers
---How to display
---Problems in salary statistics
---An example of statistical data problem
---Sum up the data based on the given conditions
---Ten columns of data combined into one column
---Count the number of Chinese characters
---Regarding data retrieval
---Count the number of non empty cells in a cell
---Automatically fill in the top ten product codes and sales figures in the table
---Count the maximum number of consecutive occurrences
---How to count the specific number of people in a column with duplicate names
---The problem of counting
---How to divide classes and count the number of men and women
---Discovering duplicate items in hundreds or thousands of data points
---Count the number of data that are different from each other
---Merge and calculate cells from multiple worksheets
---Character statistics in a single cell
---What formula should be used for the total sum of items containing a certain character in the data area
---How to implement group encoding for functions
---Take integer function
---Value rounding
---A function for finding the remainder
---Rounding formula
---Round the numbers
---How to achieve "seeing points and entering elements"
---Round off
---How to round to two decimal places
---Round the specified value based on the given number of digits
---Round to the nearest integer
---How to achieve rounding to the nearest three
---How to set cells to be rounded to thousands of yuan
---What is the solution to rounding the ROUND function without carrying?
---Keep one decimal place
---How to Round Up
---Alternative rounding
---I want to remove the decimal point and the digits after it, don't round them up or down
---Find the true rounded number
---Decimal point carry
---Single digit return to 0 or 5
---Calculate the average value
---The issue of average score
---How to find the maximum value (minimum or median)
---How to get the average
---Remove the formulas for the two maximum and two minimum values
---Go to the highest and lowest scores in a row and calculate the average value
---Remove the highest and lowest values from the 9 values and take the average
---Find the maximum value (n columns)
---How to calculate the mean only for numbers that are not equal to zero?
---Get the maximum or minimum number in the cell number group
---Mark the 3 maximum and minimum values
---The method of selecting the top five and the bottom five
---How to use a formula to find the row where the maximum value is located?
---Request multiple highest scores
---How to find the average of multiple conditions
---I want to find the third largest value
---Search for order formula
---How to achieve precise queries
---Search and reference
---Application of search function
---How can one easily determine how many specified characters are contained in a cell?
---How to use search function
---The problem of date search
---How to automatically search for the same cell content
---Search function
---How to find the right seat (search)
---A problem with text search
---Find the last numerical value in a column
---Find duplicate characters
---Please inquire about finding replacement issues
---Summary of IF Function Replacement Method
---The search function (finding the last word phrase)
---How to automatically retrieve the last data from raw data
---Find the position corresponding to the same value in two columns of data
---Find two data formulas (basic search functions are VLOOKUP and MATCH)
---Skills for inputting data
---Do not enter duplicate numbers in a column
---Cell input
---Massive 0-value input super skill
---How to enter the job number in column C and display the name in column D
---How to do input prompts
---Provide prompts before inputting information
---Data validity