Sharing Tips and Tutorials for Excel

Tips and Tricks for Excel

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...

Read More... Free Download...

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
More than 300 powerful features; Works with Office 2007-2021 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Read More... Free Download...

Table of Contents for Excel Category List
1. File

1.1 Import & Export
- 1.1.1 Import text, csv, xml, file or worksheet/workbook
- 1.1.2 Export graphics, ranges, worksheets, and others
1.2 Print
- 1.2.1 Print areas, selections, pages, sheets, and workbooks
- 1.2.2 Special Print (print ignoring blank, print in reverse order, print titles, etc.)
1.3 Views
- 1.3.1 View Settings (default view settings, freeze panes, Excel windows, zoom, etc.)
- 1.3.2 Show / Hide (Excel window elements, zeros, etc.)
1.4 Excel Options

Editing options, save & recover options, trust center, and so on.

1.5 Excel Templates

Create, edit, and apply templates in Excel.

1.6 File Format Converter

Convert between Excel workbooks and PDF / CSV / HTML / TXT files, and convert between XLSX files and XLS files.

1.7 Open, save, or close workbooks

1.8 Page Setup

Set header and footer, page numbers, page breaks, watermark, and others in Excel workbooks.

1.9 Other Tips

2. Edit

2.1 Change Cell Content
- 2.1.1 Add characters, prefix/suffix, space, zeros, etc.
- 2.1.2 Change case, reverse text orders, or others
2.2 Combine
- 2.2.1 Combine cells
- 2.2.2 Combine rows, columns, sheets, workbooks, or tables
2.3 Convert
2.4 Copy & Paste
2.5 Clear & Delete
- 2.5.1 Clear content and formatting of cells or objects
- 2.5.2 Delete cells, rows, or columns
- 2.5.3 Delete characters, spaces or others
2.6 Extract Data
- 2.6.1 Extract numbers, text, or dates from cells
- 2.6.2 Extract data from range, objects, or others
2.7 Fill Cell Content
- 2.7.1 AutoFill or fill cells
- 2.7.2 Insert date/time or sequence numbers
- 2.7.3 Insert random data or workbook information
2.8 Find
- 2.8.1 Find blank cells or find by values / formatting / position
- 2.8.2 Search scope and others
2.9 Hide or Unhide
- 2.9.1 Hide or unhide rows, columns, or sheets
- 2.9.2 Hide or unhide formulas, cell contents, or others
2.10 Select
2.11 Replace
- 2.11.1 Replace characters, errors, or formatting
- 2.11.2 Replace scope and others
2.12 Go to Cells, Rows, Sheet, etc.

2.13 Move Characters, Cells, Rows, Columns, or Sheets

2.14 Rename Sheets, Workbooks, etc.

2.15 Split Cells, Columns, or Ranges

Split cells by delimiters or width, split names, split text and numbers, split to rows, and split columns/range, etc.

2.16 Other Tips

3. Insert

3.1 Chart
3.2 Illustrations
- 3.2.1 Insert Pictures
- 3.2.2 Insert Textbox
- 3.2.3 Insert Shapes and others
3.3 Links
- 3.3.1 Create, remove, and open hyperlinks
- 3.3.2 Create, break, find links and others
3.4 PivotTable and PivotChart
- 3.4.1 Data source and fields of PivotTable
- 3.4.2 Sort, filter, group, and format PivotTable
- 3.4.3 Refresh PivotTable and others
- 3.4.4 PivotChart
3.5 Insert Rows and columns

3.6 Insert Bullets and numbering

3.7 Insert List of values

Custom list, list of combinations or permutations, list of dates or weekdays, list of sheets or files or folders, list of unique values, etc.

3.8 Insert Sheets and workbooks

3.9 Other Tips

4. Format

4.1 Format Cells
- 4.1.1 Format cell alignment and border
- 4.1.2 Format font and fill color for cells
- 4.1.3 Format number cells and others
4.2 Conditional formatting
4.3 Auto format, row height/column width, save format style, and others

5. Data

5.1 Data Validation
- 5.1.1 Drop-down list
- 5.1.2 Typing restriction
- 5.1.3 Other data validations
5.2 Filter
- 5.2.1 Auto filter & number/date/text filter
- 5.2.2 Filter by criteria & clear filter
- 5.2.3 Other filter
5.3 Sort
- 5.3.1 Auto sort & advanced sort
- 5.3.2 Sort numbers, dates, or sheets
- 5.3.3 Sort randomly/horizontally or reverse sort
- 5.3.4 Sort by list/formatting or others
5.4 Check characters, value, file, etc.

5.5 Compare cells, columns, or sheets

5.6 Group or ungroup data

5.7 Other tips

6. Review & Security

6.1 Protect cells
- 6.1.1 Protect cells, formulas, or cell formatting
- 6.1.2 Protect cells by criteria or others
6.2 Protect sheets

Automatically protect worksheets, protect multiple worksheets in bulk, edit protected worksheets, and prevent operations in protected worksheets, etc.

6.3 Protect workbooks

Protect multiple workbooks, protect workbook templates, and share workbooks.

6.4 Comments

Insert or delete comments, edit comment contents, export comments, format comments, show or hide comments, convert between comments and cell contents, and others.

6.5 Spelling check track changes, and others

7. Calculation & Formulas

7.1 Look for a value
- 7.1.1 Vlookup
  Vlookup and return multiple or special values, Vlookup by max/min value or special criteria, vlookup in special order or special range, vlookup with case sensitive, and so on.
- 7.1.2 Find closest value, find most common value, find value by position, and others
7.2 Date & Time
7.3 Math Functions
- 7.3.1 SUM
  Sum with criteria, sum across sheets, sum excluding cells, sum until meeting condition, sum visible cells only, and sum calculations.
- 7.3.2 ROUND
  Round up, round down, round even, round odd, round date/time, etc.
- 7.3.3 ABS, SQTR, SUMIF, and SUMPRODUCT
7.4 Statistic Functions
- 7.4.1 AVERAGE
  Calculate average by date or criteria, average ignoring zeros or special values, weight average, and other average calculations.
- 7.4.2 COUNT
  Count cells with criteria, count characters or works in cells or ranges, count days between two dates, count duplicate, unique values, or other special cells, count ignoring cells, count sheets or workbooks, and other counting calculations.
- 7.4.3 MAX & MIN
  Find max or min value with criteria, find max or min value in a group, find max or min excluding special values (says 0), find the nth highest or smallest values, etc.
- 7.4.4 RANK
  Calculate rank percentile, rank with criteria, rank across worksheets, rank ignore special values (says 0), etc.
- 7.4.5 MEDIAN, PERCENTILE, QUARTILE, etc.
7.5 User Defined Functions
- 7.5.1 Edit Cells
  Apply user defined functions to change cell content, combine cells/rows/columns, convert, delete or clear cell content, extract content, fill cell content, split cell content, and so on.
- 7.5.2 Calculation
  Apply user defined functions to statistic, do math calculations, look for values, date/time calculations, and so on.
- 7.5.3 Inserting, formatting, data, etc.
7.6 Formula Examples

This section lists articles that use formulas to solve problems in Excel. The articles and Excel functions applied in the corresponding articles are separated and displayed in two columns.

7.7 Formula Applications

Do addition, subtraction, multiplication, division with formulas, calculate percentages, calculate interest/payment/tax/price, etc.

7.8 Calculation Options

Hide, highlight formula errors, or replace formula errors with 0, blank, or certain text; ignore blank or zero, show or hide formula, show or hide trace arrows, update formulas, and so on.

7.9 Other Formula Tips

Change, lock cell references in formulas, copy formulas, replace formulas with their calculation result, named ranges, and other formula tips.

8. Developer

8.1 Controls
- 8.1.1 Buttons
  Create, change, and format command buttons in Excel, etc.
- 8.1.2 Check Box, combo box, and list box
  Create, format, link check boxes, combo boxes, and list boxes.
- 8.1.3 Option button and other controls
8.2 Macros & VBA
- 8.2.1 File
  Apply VBA code to set Excel options, import/export worksheets, workbooks, graphics, csv/text/pdf files, save/close/open workbooks, configure page setup, print, view, etc.
- 8.2.2 Insert
  Apply VBA code to insert illustrations, links, lists, PivotTable/PivotChart, rows/columns, and other objects in Excel easily.
- 8.2.3 Edit
  Apply VBA code to change cell content, combine cells/ranges/sheets, convert numbers/file format, copy/paste, delete or clear content and formatting, fill cell content, find and replace, go to cell, range, or sheets, etc.
- 8.2.4 Format
  Apply VBA code to configure conditional formatting, format cells, and so on.
- 8.2.5 Data
  Check data with VBA code, or apply VBA code to set data validation, filter, sort, etc.
- 8.2.6 Review & Security
  Apply VBA code to add, change, format comments, protect cells/sheets/workbooks, and so on.
- 8.2.7 Calculation
  Use VBA code to configure calculation options, do math calculations (including sum, count, average, round, rank, etc.), and others.
- 8.2.8 Developer
  Apply VBA code to insert, format, or delete controls, macros, and others.
- 8.2.9 Mailings and others
  Apply VBA code to send emails, set signatures, add attachments, and other operations in Excel as well.
8.3 Other Developer Tips

9. Shortcut Keys

Many users prefer to shortcut keys to help themselves work more effectively in Excel. This section collects articles which use frequently used shortcuts, function keys, etc. to solve problems in Excel. For example, press Ctrl + ; keys to insert the current date, press Shift + Enter keys to jump to the last cell of current selection, and so on.

10. Google Sheets

As an online spreadsheets of Google apps, Google Sheets is an easy-to-use application to help users work online and collaborate with others no matter you are working in your office, home, or somewhere else. This section lists some tutorials about how to use the Google Sheets, such as adding prefix to multiple cells in Google sheets, changing cases in Google sheets, and so on.

11. Other Excel Tips

This section lists articles about mailings skills in Excel, and articles that cannot be grouped to above categories/sections.

The End

List of articles in category Tips & Tricks for Excel
Title
How to extract capital letters or words starting with capital letter from text strings?
How to save a workbook as utf-8 encoding file?
How to remove all instances of a character in Excel range?
How to quickly determine or extract AM and PM from DateTime string in Excel?
How to insert or showing Power View in Excel?

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Tips and Tricks for Excel

Table of Contents for Excel Category List

1. File

1.1 Import & Export

1.2 Print

1.3 Views

1.4 Excel Options

1.5 Excel Templates

1.6 File Format Converter

1.7 Open, save, or close workbooks

1.8 Page Setup

1.9 Other Tips

2. Edit

2.1 Change Cell Content

2.2 Combine

2.3 Convert

2.4 Copy & Paste

2.5 Clear & Delete

2.6 Extract Data

2.7 Fill Cell Content

2.8 Find

2.9 Hide or Unhide

2.10 Select

2.11 Replace

2.12 Go to Cells, Rows, Sheet, etc.

2.13 Move Characters, Cells, Rows, Columns, or Sheets

2.14 Rename Sheets, Workbooks, etc.

2.15 Split Cells, Columns, or Ranges

2.16 Other Tips

3. Insert

3.1 Chart

3.2 Illustrations

3.3 Links

3.4 PivotTable and PivotChart

3.5 Insert Rows and columns

3.6 Insert Bullets and numbering

3.7 Insert List of values

3.8 Insert Sheets and workbooks

3.9 Other Tips

4. Format

4.1 Format Cells

4.2 Conditional formatting

4.3 Auto format, row height/column width, save format style, and others

5. Data

5.1 Data Validation

5.2 Filter

5.3 Sort

5.4 Check characters, value, file, etc.

5.5 Compare cells, columns, or sheets

5.6 Group or ungroup data

5.7 Other tips

6. Review & Security

6.1 Protect cells

6.2 Protect sheets

6.3 Protect workbooks

6.4 Comments

6.5 Spelling check track changes, and others

7. Calculation & Formulas

7.1 Look for a value

7.2 Date & Time

7.3 Math Functions

7.4 Statistic Functions

7.5 User Defined Functions

7.6 Formula Examples

7.7 Formula Applications

7.8 Calculation Options

7.9 Other Formula Tips

8. Developer

8.1 Controls

8.2 Macros & VBA

8.3 Other Developer Tips

9. Shortcut Keys

10. Google Sheets

11. Other Excel Tips

The End

Follow Us