Excel Learning notes-sorting, filtering, finding, positioning, subtotals and data validation and details manipulation tips

Source: Internet
Author: User

Excel Study Notes

Course Content: Find, replace, position

Target content you want to achieve:

    1. 1. Replace the specified content, for example: Suzhou <-Suzhou Red background color <-yellow background color Replace "Zhang" with "manager's relative"
    2. 2. Locate the cell in a specific location, similar to the filtering function (the Locate button is not found in Mac system)
    3. 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:

    1. Example CONTROL + F = "Find Command"
    2. upper left corner Select Table first cell hold shift+ control + arrow key →+ arrow key ↓ quickly select entire table
    3. First select an area, fill the upper left cell, control + ENTER to fill the upper left cell value into the selected area at once
    4. After the name box is named in the common operations area, you can directly locate the relevant area cell by name
    5. 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. 1. Multi-conditional sorting (multi-variable sequencing), cell sorting of the same nature
    2. 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:

    1. 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.
    2. 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

    1. Positioning F5 shortcut key reference conditions set

    1. Freeze first row and first column view-freeze window

    1. 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?
    2. Row height, column width, font, and font size for the same type of data, as much as possible
    3. 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
    4. Shortcut key actions: ctrl+d/r, copy upstream data/left column data Ctrl + Up/down, jump to the bottom of the table
    5. Shortcut key actions for cell formatting
    6. F4 shortcut keys repeat the previous action
    7. "'" to convert an auto-convert hyperlink to a text format

For further understanding:

    1. 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.
    2. 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.