Buzz
PART I: SOME HANDY EXCEL TRICKS PART 1
1. Mastering Some Basic Operations in Excel
- Setting Up Print: Page Setup
- Spreadsheet operations, data on spreadsheet: formatting, merging cells, deleting, table borders…
- Using common functions in Excel: sum, average, Vlookup…
- Creating charts: Chart.
- Creating hyperlinks in Excel spreadsheets: Hyperlink
- Protecting data on a sheet.
2. Convert Data from Rows to Columns and Vice Versa
To convert data from rows to columns, simply copy the data table -> paste it back by selecting Paste Special -> check the Transpose box to convert columns to rows and vice versa:
3. Remove Duplicate Data
For example, in a column containing duplicate data, to remove duplicate data and keep only one unique value -> highlight the data range that needs to remove duplicate data -> go to the Data tab -> check the Remove Duplicates option:
4. Create Additional Quick Access Keys
To create additional quick access keys, you can check existing keys on the toolbar such as keys to create a new spreadsheet New, Save…
If you want to create keys that are not available on the toolbar, select More Commands -> a dialog box appears choose the key to create -> select Add:
5. Select and Add or Remove Entire Rows or Columns Using the Keyboard
- To select a row at the mouse pointer position, press the keyboard shortcut: Shift + Spacebar
- To select a column at the mouse pointer position, press the keyboard shortcut: Ctrl + Spacebar
- To add a row or column, press the keyboard shortcut: Ctrl + Shift + “+”
- To delete a row or column, press the keyboard shortcut: Ctrl + “-“
6. Add a Diagonal Line in a Cell
Right-click on the cell where you want to create a diagonal line -> select Format Cell -> a dialog box appears click on the Border tab -> choose the direction of the diagonal line to create -> click OK:
7. Limit Input Values Using Data Validation in Excel
To avoid major numerical value mix-ups, Excel provides the Data Validation feature to limit the values entered in a predefined data range. To do this, select the data range you want to enter -> go to the Data tab -> Data Validation -> a dialog box appears, choose the type of restriction for your values:
8. Copy or Move Data Ranges Extremely Quickly
- Copy data extremely quickly by selecting the data range to be copied -> hold down the Ctrl key -> move to the position to be copied.
- Move data ranges quickly by holding down the left mouse button and dragging to the new position.
9. Applying Excel's filter function to accounting books
A very useful feature in Excel is the data filter feature - This feature helps you minimize search time, extremely fast operations. To apply the filter feature, you need to select the title of the data area -> go to the Data tab -> Filter -> the title row appears the dropdown arrow you just need to click on the arrow to select the data according to your requirements, for example, here just want to know the information of students of Cẩm Thịnh Secondary School:
10. Managing goods with Data Validation in Excel
You want to fix the existing item categories to avoid cases where users enter incorrect data. Data Validation helps you manage goods closely and extremely simply by creating lists available for fixed item categories.
To use Data Validation, you need to create data for the list -> select the data area to create -> go to the Data tab -> Data Validation -> the dialog box appears in the Allow section choose List, the Source section select the data area you want to create a list:
The result of creating a list helps you manage goods better:
11. Deleting empty cells in a data range
Your data contains many empty cells, but the empty cells are not adjacent, so you have to repeat the operation many times to delete. In this article, we guide you on how to quickly delete non-adjacent empty cells
- Select empty cells in the data table by enabling the filter feature in Excel -> check blank:
Only the empty data areas are displayed, the rest you just need to select all empty data -> right-click select Delete:
12. Smart search in Excel
You don't remember the search phrase clearly, the simplest way is to use the smart search feature in Excel by combining the character “*”. Excel searches for all keywords related to the phrase enclosed in “*”:
13. Speed up data entry by a hundredfold
A simple way to speed up data entry is to use shortcut keys to replace the content to be entered. You select the word or phrase commonly used in the spreadsheet to replace.
Step 1: Go to the File tab -> Option -> the dialog box appears select Proofing -> click AutoCorrect:
Step 2: Select the phrase to replace the content to be displayed in the Replace section, the content is displayed through the abbreviation in the With section -> click Add:
For example, here when typing chxh -> press space the displayed content changes to Socialist Republic of Vietnam.
14. How to create a Drop-down list in Excel
Very simple, after creating the data range displayed in the Drop-down list -> select the cell position to create the Drop-down list -> go to the Data tab -> Data Validation -> the dialog box appears in the Allow section select List the Source section select the data source in the Drop-down list -> press OK:
The result of creating a Drop-down list helps you enter data faster and minimizes data entry errors:
15. Hide all data
For some reason, on the spreadsheet, you need to hide content so that users cannot read it, but you can still observe and calculate on the spreadsheet. You select the data range you want to hide -> right-click select Format Cell in the Custom section enter 3 consecutive semicolons:
The result is that the data is hidden but the value is still displayed in the formula bar, which allows you to still manipulate and process the hidden data:
16. Convert uppercase and lowercase letters
To convert quickly, you use the following 2 functions:
- Upper() Function: Convert lowercase to uppercase:
- Lower() Function: Convert uppercase to lowercase
17. Rename a sheet by double-clicking
The fastest and simplest way to rename a sheet is to double-click the sheet name you want to rename -> enter a new name for the sheet -> press Enter.
18. Concatenate data with the & key
For example, you have 3 fields: first name, middle name, and last name. You want to concatenate the 3 columns into 1 column Name, you just need to add the & key between the values of the 2 columns:
19. Enter data starting with the number 0
Usually when entering a phone number, you cannot enter the first 0 to overcome this disadvantage, you enter a single quote before the data to be entered:
Or you can go to Format cell select Custom in the Type section enter the number of 0s you want to display:
Result:
20. Quick view statistics about total, mix, max,...
Sometimes you want to quickly check about total min, max... without using formulas, you just need to glance quickly at the bottom toolbar of Excel which displays quite comprehensive information:
21. Repeat Format Painter operation
You want to use the format of the previous object then Format Painter is indispensable, however, you want to apply to many objects, instead of selecting the object containing the format to be copied -> click the brush icon -> select the object you want to apply the format to multiple times after the first time you hold down the Ctrl key the rest you just need to apply the format to the object you want to apply without reselecting the format to copy.
Here are some extremely useful Excel tricks you need to know - Part 1. In part 2, I will continue to introduce you to shortcuts to help you use Excel quickly and efficiently.
Refer to part 2 here:
