PART I: SOME USEFUL EXCEL TIPS PART 1
1. Mastering some basic operations in Excel
- Print page settings: Page Setup
- Working with spreadsheets, data on spreadsheets: formatting, merging cells, deleting, drawing tables .
- How to use common functions in Excel: sum, average, Vlookup .
- Graph: Chart.
- Create hyperlinks in Excel spreadsheets: Hyperlink
- Secure data on sheet.
2. Convert data from row to column and vice versa
To convert data from a row to a column, you only need to copy the data sheet -> paste it again by selecting Paste Special -> tick Transpose to convert the column to row and vice versa:
3. Delete duplicate data
For example, in the column that contains duplicate data, to delete duplicate data, only retain only one value -> highlight the data area to be deleted -> go to the Data tab -> select Remove Duplicates:
4. Create quick access keys on Quick Access
To create a new access key you can select the available keys on the bar such as the key to create a new workbook New, Save .
In case you want to create a key that is not available on the toolbar, click More Commands -> the dialog box appears and select the key to create -> click Add :
5. Select and add rows and columns completely using the keyboard
- To select a line at the cursor position press the key combination: Shift + 'space'
- To select a column at the position of the mouse cursor, press Ctrl + 'space'
- To add a row, press Ctrl + Shift + '+'
- To delete a row, press Ctrl + '-'
6. Add 1 diagonal in 1 cell
Right-click the cell you want to create a diagonal -> select Format Cell -> dialog box appears, click the Border tab -> select the diagonal direction to create -> click OK:
7. Limit the input value using Data Validation in Excel
To avoid confusion about the value of Excel numbers, Data Validation feature helps you limit the input value in a predefined data range. To do that you need to select the data range to enter -> on the Data tab -> Data Validation -> the dialog box appears selecting the type of range limitation for your value:
8. Copy or move data areas very fast
- Copy data very quickly by selecting the data area to be copied -> hold down Ctrl -> move to the location to be copied.
- Move the data area quickly by holding and holding the left mouse button and dragging it to a new position.
9. Application of Excel filter function in accounting books
A very nice and extremely useful feature in Excel is the data filtering feature - This is a feature that helps you minimize the time of searching and manipulating extremely fast. To apply the filtering feature, you need to select the title of the data area -> go to the Data tab -> Filter -> the subject line appears down arrow, you just need to click on the arrow to select the required data For example, here I just want to know the information of Cam Thinh Secondary School's students:
10. Manage goods with Data Validation in Excel
You want to fix the list of available goods to prevent users from entering the wrong data. Data Validation helps you to manage your goods closely and simply by creating available lists for fixed goods lists.
To use Data Validation Need to create data to list -> select the data area to create -> go to Data tab -> Data Validation -> dialog box appears in Allow list selection , Source select data areas want to create list:
The result has created a list to help you manage goods better:
11. Delete blank cells in a data range
Your data exists many blank cells, but the blank cells in the position are not adjacent to each other, so to delete you must perform the operation repeatedly. In this article, guide you to delete non-adjacent blank cells fastest
- Select blank cells in the data table by turning on the filter feature in Excel -> check blank:
The result will show only the blank data areas, all you have to do is select the entire blank data area -> right-click and select Delete:
12. Smart search in Excel
You don't remember search terms clearly, the simplest way is to use the smart search feature in Excel by combining the '*' character. Excel performs a search of all keywords related to the phrase in the '*' pair:
13. Speed up inputting hundred times
A simple way to help you speed up your input is by using keyboard shortcuts to replace what you need to type. Please select the words or phrases that are commonly used in the selection spreadsheet instead.
Step 1: Go to File -> Option -> Dialog box, select Proofing -> click AutoCorrect:
Step 2: Select the replacement phrase for the content to be displayed in the Replace section, the content is displayed through the abbreviation phrase in the With -> section, click Add:
For example, when typing chxh -> press the spacebar to display the content converted into the Socialist Republic of Vietnam.
14. How to create drop-down lists in excel
Quite simply, after creating the data displayed in the Drop- down list -> choose the location you want to create box Drop- down list -> card Data -> Data Validation -> dialog box appear in Allow selected list items source selection data sources located in Drop- down list -> click OK:
The result has created a drop-down list to help you input faster and minimize the situation of wrong data entry:
15. Hide all data
For some reason, the spreadsheet needs to hide content that is not readable by the user, and you can still observe and calculate on the spreadsheet. You select the data area you want to hide -> right-click and select Format Cell in the Custom field, enter 3 consecutive semicolons:
The results of the data are hidden, but the value is still displayed in the formula bar, which helps you still manipulate and process it on hidden data:
16. Convert uppercase and lowercase letters
For fast conversion you use the following 2 functions:
- Upper (): Converts lowercase to uppercase:
- Lower function (): convert capital letters to lowercase
17. Rename the sheet by double-clicking
The quickest and simplest way to rename a sheet is just double click the name of the sheet to be renamed -> type a new name for the sheet -> press Enter.
18. Pair data with & key
For example, you have 3 field names, middle names and first names. You want to concatenate 3 columns into 1 column First and last name just add the value between 2 columns with & sign:
19. Enter the data starting with 0
Normally when entering the phone number you can not enter the first 0 to overcome that drawback you enter apostrophe before the data to enter:
Or you can go to Format cell and select Custom in the Type field, enter the number of 0 you want to display:
Result:
20. See quick statistics on sum, mix, max, .
Sometimes you want to quickly check the total min, max . without using formulas, you just need to glance at the toolbar at the bottom of the Excel table to display quite enough information:
21. Repeat the Format Painter operation
If you want to use the format of the object first, the Format Painter is indispensable, but you want to apply it to many objects, instead of selecting the object containing the format to copy -> click the brush -> select select the object you want to apply the format many times after the first time you press and hold the Ctrl key, you just need to apply the format to the object you want to apply without selecting the format to copy.
Above are some extremely useful Excel tips to know - Part 1. In Part 2, I will introduce to you the shortcuts that help you use Excel in the fastest and most effective way.
Refer to part 2 here: