Buzz
Explore an additional set of 100 invaluable Excel tricks in Part 2 to optimize your speed and achieve maximum efficiency in tasks.
22. Secure Your Excel File with a Password
Ensure data security in your Excel file by setting up a password. Only those with the password can access the detailed content. Simply create a password for your Excel file by navigating to the Review tab -> Protect Workbook:
Enter the password to secure your file. This way, before opening an Excel file, it will prompt you for the password. If the password is correct, only then can you view the content within the file.
23. Magic in Excel with Conditional Formatting
It's truly regrettable if you overlook this feature. This incredibly useful function helps you conditionally format various attributes of objects, such as coloring values that satisfy conditions, paying attention…
24. Concatenate First and Last Names, Create Space in Between
You have columns of data containing last name, first name, and middle name. You want to merge them into a single column with all the fields: last name, first name, middle name. It's simple, just use the & symbol and insert a space character between the words.
25. Select All Data with a Single Mouse Click
To select all data, simply click on the top-left cell of the spreadsheet. This is extremely useful when you need to format the spreadsheet:
26. Hide Data in a Cell
To make the data in a cell invisible to users while still being displayed on the formula bar for calculations, select the data range you want to hide -> right-click -> choose Format Cell -> select Custom -> enter 3 semicolons in the Type field.
The result data is now hidden, and you can apply this method to various data types:
27. Quick Uppercase and Lowercase Writing
If you've accidentally entered data in uppercase (or lowercase) but need it in lowercase (or uppercase) according to the standard, don't rush to delete and re-enter. Instead, use the built-in Excel function:
- To convert uppercase to lowercase, use the Lower() function
- To convert lowercase to uppercase, use the UpCase() function
For example:
28. Open Multiple Excel Files at Once
In your folder, there are multiple Excel files, and you want to open all of them. Instead of individually selecting and opening each file, press Ctrl + A to select all the files and then press Enter. This way, you can open multiple Excel files at once with just one action.
29. Convert Data from Thousands, Millions to K, M
215k is a common abbreviation today, both helping to shorten numerical values and speeding up calculations. It's not difficult at all – just select the data range you want to convert to K, M -> right-click and choose Format Cell, or press the shortcut Ctrl + 1 -> the Format Cells dialog appears, select Custom -> in the Type field, enter “###”,”k” -> press OK:
The result is data created with the unit K:
Similarly for the M format, perform the same steps as above.
30. Add Multiple Rows at Once in Excel
To add multiple rows at once, select the number of rows you want to add -> right-click and choose Insert. You will add the exact number of rows you selected, for example:
31. Automatically Fill a Series
Numbering sequentially is not difficult, but when the list reaches thousands of records, using drag-and-drop is impractical and challenging. To fill the STT column, follow these steps:
Step 1: Enter all the content except the STT
Step 2: Enter the first 2 values of the STT column -> select the 2 cells you just entered -> a black plus sign appears -> double-click on that icon -> you have filled the STT to the last row containing data. Note that the rows must be contiguous, without gaps, and without blank rows:
32. Quickly Format a Table in Excel
Instead of performing multiple steps, simply press the shortcut Alt + H + T, and the dialog appears with formatting options:
33. Freeze Top Row, First Column
To freeze the top row or first column, go to the View tab -> Freeze Panes with the following choices:
- Freeze Top Row: Freezes the top row
- Freeze First Column: Freezes the first column
34. Select Random Numbers on a Spreadsheet
If you want to generate random numbers, you don't have to think twice. Use the Randbetween(Bottom, Top) function to create random numbers, where Bottom, Top are the limits of the range of values for the random numbers generated within that range.
35. Convert Numeric Data to Date Format
To convert numeric data to date format, follow these steps: Select the data cell you want to convert -> go to the Data tab -> Text to Columns -> the wizard appears, click Next, choose the data type format Date -> Finish:
36. Filter Data in Excel
Save a lot of time and effort by choosing the data filter feature in Excel. Just select the header row, go to the Data tab, and choose Filter:
37. Utilize Sparkline in Charts
For the most understandable chart representation, insert Sparklines into your chart. It's straightforward - go to the Insert tab -> Insert Line or Area Chart:
38. Change Default Font in Excel
To change the default font, press the shortcut Alt + T + F.
39. Interaction between English and Excel
An important aspect you need to know when learning Excel is English proficiency. Mastering English is an advantage that significantly enhances your Excel skills.
40. Excel is an Indispensable Daily Tool
Another simple way to excel in learning Excel is to work with it regularly. Consistent practice helps you remember and perform your tasks well.
41. Select Data within an Excel Cell
To navigate quickly within a cell, you need to be familiar with the following shortcuts:
- Shift + Right or Left Arrow: Move right or left by 1 character.
- Ctrl + Shift + Right or Left Arrow: Move right or left by 1 word.
- Shift + Home: Move to the start position of the cell.
- Shift + End: Move to the end position of the cell.
42. Delete Data within an Excel Cell
- To delete data to the left of the cursor, press Backspace
- To delete data to the right of the cursor, press Delete
- To delete formula data from the cursor position to the end of the cell, press the shortcut Ctrl + Delete
43. “Enter” – Input Data
Several keyboard shortcuts combined with the Enter key assist you in fast data input:
- Enter: Move to the next line below.
- Shift + Enter: Move upwards.
- Shift + Tab: Move the cursor to the left.
- Tab: Move to the right.
- Ctrl + Enter: Stay at the current mouse cursor position.
44. Input Data for a Data Range
To input data for a range, simply select the data range -> enter content -> press Ctrl + Enter to move the input position. Make sure you only input data within the selected range.
45. Copy Formulas from Cell Above
To minimize mouse actions, you can use key combinations after copying data:
- Press the shortcut Ctrl + Single Quote: to copy the formula from the cell above.
- Press the shortcut Ctrl + Shift + Single Quote: to copy the formula from the cell below.
46. Insert Hyperlinks
To insert a hyperlink, go to the Insert tab -> Links:
47. Open Font Settings Dialog
Quickly choose a font for the spreadsheet by pressing the shortcut Ctrl + Shift + F.
48. Strikethrough Text
A simple way to create strikethrough text without mouse actions, press the shortcut Ctrl + 5
49. Align Left, Right, Center within a Cell using Alt +
Some shortcuts to help you align content within a cell:
Alt + H A L: Align left.
Alt + H A R: Align right.
Alt + H A C: Align center.
50. Increase or Decrease Font Size
Some shortcuts to help you adjust font size:
Alt + H F G: Increase font size.
Alt + H F K: Decrease font size.
51. Create and Remove Cell Borders
- To create cell borders, press Ctrl + Shift + &
- To remove cell borders, press Ctrl + Shift + “-“
52. Open Excel Formula Dialog Box
Quick shortcut to open the Excel formula dialog box is to press Shift + F3
53. Expand Formula Bar
To expand the formula bar, press the shortcut: Ctrl + Shift + U
54. Name a Data Range
Quickly name a data range by pressing the shortcut Ctrl + F3
55. Create a New Sheet
No need for mouse clicks, just press Shift + F11 to create a new sheet
56. Enable Shortcut Keys in Excel
To enable shortcut keys in Excel, simply press the shortcut Alt + T + A
Here is a detailed introduction to the tricks in the 100 useful Excel tricks series.
Refer to Part 1 here:
