Excel Study Notes
Course Content: Find, replace, position
Target content you want to achieve:
- 1. Replace the specified content, for example: Suzhou <-Suzhou Red background color <-yellow background color Replace "Zhang" with "manager's relative"
- 2. Locate the cell in a specific location, similar to the filtering function (the Locate button is not found in Mac system)
- 3. Comment Modify Delete transform shape Hide command
use of tools, means (Name of the action button, location):
1.1 Find and replace-option-cell match to lock the specified cell to avoid errors like "Suzhou City"
1.2 Using wildcard characters for "Fuzzy Lookup" operation two wildcard characters shift+ 8 = *: denotes multiple fingers? (English question mark): Represents a single reference
2.1 Name box positioning
2.2 Select cells with cell properties in the positioning condition second cell empty value object Visible cells
Related shortcut command actions:
- Example CONTROL + F = "Find Command"
- upper left corner Select Table first cell hold shift+ control + arrow key →+ arrow key ↓ quickly select entire table
- First select an area, fill the upper left cell, control + ENTER to fill the upper left cell value into the selected area at once
- After the name box is named in the common operations area, you can directly locate the relevant area cell by name
- Blank cell hit "=" + "Arrow key ↑" Drag to fill all empty cells directly into the contents of the cells above them.
error-prone: No cell matches unselected area, resulting in a full document-wide substitution.
Excel Study Notes
Excel The ultimate goal is to eliminate all repetitive operations
Course content: Subtotals and data validation
Target content you want to achieve:
1.1 Subtotal Total Data Merge cells
1.2 Nesting of subtotals
1.3 Copy the result area of a subtotal
2.1 Data validation set a precondition (integer data, text length data, sequence data)
2.2 Switching IME
2.3 Cell Information Protection
use of tools, means (Name of the action button, location):
Core idea: What sort of summary to summarize (summary method)
Data-Subtotal Sort operation All Delete button multi-conditional sort paste-in-visible cell
Data-data validation custom formula gives data that is always false, which protects the data from being modified
Related shortcut command actions: example Double-click the border to automatically adjust the cell width so that the length matches the content
Alt + '; ' Visible cell shortcut keys action
Easy Error Point:
1. Sorting is not done before subtotals
2. Replace the current Subtotal button selection (whether or not nested on a subtotal)
3. Subtotals do not support "cash" and "cash" (more than one space is not treated as the same value)
4. "," (comma in English) must be used as a delimiter when defining sequence values
Excel Study Notes
Excel The ultimate goal is to eliminate all repetitive operations
Course content: Sorting and filtering
Target content you want to achieve:
- 1. Multi-conditional sorting (multi-variable sequencing), cell sorting of the same nature
- 2. Multi-Criteria filtering
use of tools, means (Name of the action button, location):
1. sort-Add conditional sort by custom sort
2. Advanced Filter Commands
Related shortcut command actions: Advanced Filter peers represent "and" commands, different rows represent "or" commands, and each row is a filter
Details:
- When you print a long table, you set the top header row in the page setup, and you can display the first row of header content on each page.
- Automatic interlaced insertion with filter commands
Easy Error Point:
When you filter a table, click a cell in the entire table without having to select a specific column.
The visible cells command is not selected when you copy a pasted filtered table
Select non-repeating records to directly filter by unique amount
Excel Study Notes
Excel The ultimate goal is to eliminate all repetitive operations
Course content: Pivot Table
Core content: PivotTable: Column field row field Value Field Filter field drag-and-drop according to the target table style and perform the corresponding consolidation operation
what you want to achieve: summarize the information that refines the source data
use of tools, means (Name of action button, location): Insert-PivotTable row Label-right-click Cell-Create Group data move right to the column label to achieve a transition between horizontal and vertical rows.
related shortcut commands: For example, double-click the field header to modify the field directly
Easy-to- err: Example of setting error values for cell formats, percent signs,
F1 Excel Help Document note content
Pivot table: An interactive way to quickly summarize large amounts of data
PivotChart: Graphically represent data in a PivotTable report, related to each other, and can be rendered in real time
- Positioning F5 shortcut key reference conditions set
- Freeze first row and first column view-freeze window
- Row, column headings bold, appropriate to handle the text color, fill the background color in Excel how to match the color, visually more acceptable and high degree of distinction?
- Row height, column width, font, and font size for the same type of data, as much as possible
- Define a more standard format, such as a percentage reserved several decimals, mobile phone number column width is set enough, time display as far as possible localization
- Shortcut key actions: ctrl+d/r, copy upstream data/left column data Ctrl + Up/down, jump to the bottom of the table
- Shortcut key actions for cell formatting
- F4 shortcut keys repeat the previous action
- "'" to convert an auto-convert hyperlink to a text format
For further understanding:
- Data--columns, the data in the column into a number of columns, such as "xxx Province xxx City", split into provinces, grams column, "xx hours xx minutes" into the time, divided into two columns, according to the width, text, punctuation, etc. as the definition of split, a lot of scenes will be used, please first learn.
- Power map: Online map + online demo + Make a video, come to the China Trench thermal Power View: A chart with visual interaction, ideal for presentation
Excel Learning notes-sorting, filtering, finding, positioning, subtotals and data validation and details manipulation tips