100 extremely useful Excel tips to know - Part 1

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:

100 extremely useful Excel tips to know - Part 1 Picture 1

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:

100 extremely useful Excel tips to know - Part 1 Picture 2

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 .

100 extremely useful Excel tips to know - Part 1 Picture 3

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 :

100 extremely useful Excel tips to know - Part 1 Picture 4

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:

100 extremely useful Excel tips to know - Part 1 Picture 5

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:

100 extremely useful Excel tips to know - Part 1 Picture 6

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:

100 extremely useful Excel tips to know - Part 1 Picture 7

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:

100 extremely useful Excel tips to know - Part 1 Picture 8

The result has created a list to help you manage goods better:

100 extremely useful Excel tips to know - Part 1 Picture 9

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:

100 extremely useful Excel tips to know - Part 1 Picture 10

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:

100 extremely useful Excel tips to know - Part 1 Picture 11

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:

100 extremely useful Excel tips to know - Part 1 Picture 12

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:

100 extremely useful Excel tips to know - Part 1 Picture 13

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:

100 extremely useful Excel tips to know - Part 1 Picture 14

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:

100 extremely useful Excel tips to know - Part 1 Picture 15

The result has created a drop-down list to help you input faster and minimize the situation of wrong data entry:

100 extremely useful Excel tips to know - Part 1 Picture 16

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:

100 extremely useful Excel tips to know - Part 1 Picture 17

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:

100 extremely useful Excel tips to know - Part 1 Picture 18

16. Convert uppercase and lowercase letters

For fast conversion you use the following 2 functions:

- Upper (): Converts lowercase to uppercase:

100 extremely useful Excel tips to know - Part 1 Picture 19

- Lower function (): convert capital letters to lowercase

100 extremely useful Excel tips to know - Part 1 Picture 20

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:

100 extremely useful Excel tips to know - Part 1 Picture 21

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:

100 extremely useful Excel tips to know - Part 1 Picture 22

Or you can go to Format cell and select Custom in the Type field, enter the number of 0 you want to display:

100 extremely useful Excel tips to know - Part 1 Picture 23

Result:

100 extremely useful Excel tips to know - Part 1 Picture 24

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:

100 extremely useful Excel tips to know - Part 1 Picture 25

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.

100 extremely useful Excel tips to know - Part 1 Picture 26

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:

100 extremely useful Excel tips to know - Part 2