## 1. Sort the Data by Color in Microsoft Excel

While editing a spreadsheet in Excel, you might need to change the color of some text or cells to make them more readable. And in some cases, like analyzing the data of monthly sales or wages, you can **sort the data by color in Excel**.

Go to ** Data** tab and click

**in**

*Sort***r group.**

*Sort & Filte*Choose the column you want to sort in ** Sort by** and select

**or**

*Font Color***in**

*Cell Color***.**

*Sort on*Choose the color and set its ** Order**. If there are multiple colors in the column, you can

**to create more rules. Click**

*Add Level***after all the settings.**

*OK*## 2. The Interface of Excel 2020

This tutorial is based on **Microsoft Excel 2020**. You can see the basic elements of its user interface below.

## 3. Insert Multiple Row or Columns in Excel

You probably know how to add a new row or new column in Excel spreadsheet, but if you need to **insert multiple rows or columns**, repeating this operation can’t be the best idea.

You can drag the mouse to select multiple rows (or columns) at first, then right-click the selected area and click ** Insert** in the menu. The number of rows inserted will be equal to the number of rows you selected.

## 4. Replace Specific Content in All the Worksheets

You must often create multiple sheets in an Excel file to process different but related data, and sometimes you may need to **replace a specific word or value in all the sheets** to ensure their correctness and consistency.

Instead of adjust each sheet separately, you can use a simple trick to boost the efficiency.

Press **[Ctrl+H]** in any of the sheets to open ** Find and Replace** window. Input the text you want to find and the text you want to replace with.

Hit** Options** button on the lower right. Then click the small down-arrow beside

**and choose**

*Within Sheet***instead. Thus the change will be applied to all the sheets in the workbook.**

*Workbook*## 5. Undo or Redo Your Last Action in Excel

There are always some faulty operations when dealing with complicated tables and sophisticated data in Excel. If you don’t know exactly what the spreadsheet looks like a second before, how can you recover it to the correct version?

In fact, you can simply **undo your last action** or multiple actions before by pressing **[Ctrl+Z]**.

On the contrary, if you want to apply an action to multiple discontinuous cells or even cells in different spreadsheets, you can make use of **[F4]** to **redo your last action**.

## 6. Let Excel Do the Repeated Calculations for You

To deal with a huge amount of data in Excel worksheets, you don’t need to do all the calculations by yourself or with a calculator. There’re some formulas can help you processing data quickly and also easy to use. You don’t even need to remember them specially.

To calculate the summation of several values, you just enter **=value 1+value 2+value 3** in the cell and press **[Enter]** to work it out.

To do subtractions, you can enter **=value 1-value 2-value 3**.

Moreover, you can combine **summations and subtractions **in a formula.

And don’t forget to place the cursor at the bottom-right corner of the cell and drag it (which is so called fill handle) to apply the same formula to multiple cells. In this way you can do calculations in a batch of cells at once.

## 7. Display all the formulas in Excel Worksheet

When a colleague sends you his or her worksheet, you may find it contains a lot of functions.

Want to know what **formulas** your colleague uses? It’s OK to click the cells one by one. But if you think it’s too much trouble, try using the shortcuts **[Ctrl+~]** to display all the formulas.

It can quickly display all the used formulas. Similarly, you can press **[Ctrl+~]** again to hide them.

## 8. Make Adjustments to Multiple Sheets in Batch

What if there are several worksheets with the same content and you want to **make the same changes to them all**?

It’s an easy trick:

press and hold the **[Ctrl]** key while clicking the sheet tabs in Excel.

Then whatever you do to one of these sheets, the changes will be applied to all the selected sheets at the same time.

## 9. Use Clipboard to Merge the Data from Different Tables

Each department has sent a table and you want to put them together, is there a way to merge all the data at once? In fact, you can **use clipboard to merge tables quickly** instead of cut & paste separately.

Click the small arrow at the bottom-right corner of ** Clipboard** section in

**tab to open the**

*Home***.**

*Clipboard*Then select the range of cells you want to merge and click ** Copy** button separately.

Click ** Paste All** on

**to merge all the selected data at last.**

*Clipboard*## 10. Add Commands to Quick Access toolbar

If there some features of Excel you use frequently but not included in the quick access toolbar by default, you can **add commands to quick access toolbar** manually to use them more conveniently in the future.

Just click the small arrow on the quick access toolbar and select ** More Commands…** in the menu.

Then choose the commands to add them in the ** Quick Access Toolbar** panel in

**, and hit OK to confirm it.**

*Excel Options*Next time you can find the feature directly in quick access toolbar. If you want to delete it, just repeat the process and ** Remove** it from the list in

**.**

*Quick Access Toolbar*## 11. Apply Color to Specific Cells Using Conditional Formatting

** Conditional Formatting** can change the format of specific data according to the conditions you set.

For example, if you want to highlight all the data greater than a fixed amount in a spreadsheet, you can use ** Conditional Formatting** to shade them in batch.

Just select the range of cells you want to apply the conditional format, then click ** Conditional Formatting** in

**tab.**

*Home*There are many existing rules in the drop-down list you can directly choose and use.

In my case, I want to shade all the data larger than 5000 in column B and column C. So choose ** Greater Than…** in

**. Type the number 5000 and choose a format color in the popping out window.**

*Highlight Cell Rules*## 12. Merge and Split Cells in Excel

A spreadsheet can’t be done without any adjustments and modifications. It’s also common to **merge multiple cells or split the content in one cell**.

You may already know there’s a merge button in the ** Home** tab of Excel. But if you use it to merge 2 cells with different texts, the merged cell will only keep the upper-left value and discards other values.

To merge multiple cells and keep all the contents of them, there are 2 functions you can try:

Create a new column and Enter the formula: **=A2&B2** to merge the contents in A2 and B2.

If you want to add a space between the contents of A2 and B2, just enter: **=A2&“ ”&B2**.

Or, you can also use this formula: **=CONCATENATE(A2,B2)**.

To split the content from one cell to multiple cells, or from one column to multiple columns, you can select the cells you want to split, and try using ** Text to Columns **in

**tab.**

*Data*And if there are no symbols like commas or tabs separating each field, choose ** Fixed width** to continue and create break lines by yourself.

Then specify the ** Destination** to output the data.

You can try another quick way to split data using Flash Fill.

## 13. Filter the Data by Cell’s Color in Excel

Similarly, sometimes you may also need to **single out the cells with specified color**.

Just right-click the color you want to screen out, and select ** Filter** –

*Filter***in the menu.**

*by Selected Cell’s Color*## 14. Make Images Aligned to Gridlines in Excel

When you make a spreadsheet with pictures in Excel, you might spend much time on** resizing the picture to fit in the cell**.

To save your time and effort, you can press and hold the **[Alt]** key while dragging the 4 boundaries of the picture to resize it.

You’ll find the picture is easily aligned to the gridlines of the cell, which is much more convenient.

## 15. Insert Mini Charts (Sparklines) in Excel Cells

The mini charts, also known as sparklines, can display the trends of data in small cells. They are more informative than pure text and number, but with the smaller size than ordinary charts.

To** insert a sparkline in an Excel cell**, just click the cell and switch to ** Insert** tab.

Choose a type in ** Sparklines** section, then specify the range of cells as the data source.

The sparklines can be created immediately.

## 16. Quick Access to Drop-down Menu

While filling in some repeated contents, such as gender, name and so on, you can create a drop-down list to save the time of typing the same text.

But do you know with the shortcuts **[Alt+↓]**, you can get the similar result without a drop-down list?

After typing some texts in the column, you can click the next cell and press **[Alt+****↓] **on your keyboard.

It enables you to **select an option from the existing texts** and fill it in the current cell.

## 17. Create an Uninterrupted Sequence in Excel Column

After filling a list of sequence numbers in Excel, you may find some of the data are redundant and need to be removed.

But if you delete them directly, some of the sequence numbers will be missing. You must to fill up them manually, which is too much trouble.

Here, I’ll show you a function to **create an uninterrupted sequence in Excel**.

You just need to enter the formula: **=ROW()-1** in the first cell, and drag your mouse to use fill handle filling the whole column automatically.

The sequence created in this way will always be uninterrupted even if you delete some rows in the spreadsheet.

## 18. Compare the Data in Two Columns

If you got 2 columns of similar data in Excel and want to compare them, it’s apparently not the best idea to find the difference row after row by naked eyes.

With Excel functions, you can **compare the two columns of data** using a single formula.

Create a column beside the 2 columns, then enter the formula: **=IF(A2=B2,”Same”,”Different”)** in the cell C2.

It means if the data in A2 and B2 are all the same, the text “Same” will be displayed in C2. Otherwise it will show “Different”.

After that, apply this formula to all the cells that beside the data you want to compare.

## 19. Count the Repetitions in Excel

If a data appears in an Excel column for multiple times, and you want to figure out how many times exactly it’s repeated, the **COUNTIF function** can help you to **count the repetitions**.

Create a column beside the list of data.

Enter **=COUNTIF(A:A,A1)** in B2, it will check all the data in column A and show how many times the data in A1 repeated.

## 20. Remove the Duplicates Lines in Excel

In addition to find duplicates in a spreadsheet, you may also need to remove the duplicate lines to simplify the content. Luckily Excel has a function to solve this problem directly.

First, select all the cells you want to remove duplicates from. Go to ** Home **tab, click

**in**

*Remove Duplicates***section.**

*Data Tools*Then select all the columns that may contain duplicates in the popping out window. Hit ** OK** to implement it. All the duplicate lines in the selected area will be removed right away.

## 21. Count the Data that Meet the Specific Criteria

In an Excel spreadsheet containing so much data, how can you **count the cells that meet the specific criteria**?

Excel functions can help you to get the results with a simple formula: **=COUNTIF(range,criteria)**

For example, to count all the cells with the text “Different” in column C, you can enter the formula in a blank cell: **=COUNTIF(C2:C10,“Different”)**, then press **[Enter]** to get the result.

Similarly, if you want to count all the data greater than a specific number in column A and column B, the formula can be like: **=COUNTIF(A:B,”>80″)**.

## 22. Fully Display Numbers in Excel Cells

Due to scientific notation, the long numbers (more than 11 digits) inserted to Excel cells will be abbreviated by default.

So how can you **fully display long numbers in Excel**?

One thing you can do is changing the format of these cells.

Right-click them, choose ** Format Cells** and selecting

**in the**

*Text***before entering long numbers.**

*Category*And if you want the long numbers to be displayed as phone numbers, you can change it in ** Format Cells** as well. Check the detailed steps to display numbers as phone numbers.

## 23. Calculate the Week Numbers According to the Dates

If the data of date has been collected in an Excel spreadsheet, and you want to know **what day of the week it is**, is there any way other than looking at your own calendar?

As you know, Excel functions are very powerful, there’s almost always a proper formula for solving your problems in Excel. It’s no exception in this case.

Create new column to output the week numbers beside the column of dates.

In my case the first cell of date is A2, so enter the formula: **=TEXT(A2,”AAAA”)**.

Then press **[Enter]** to work it out and apply the formula to all the cells in the new column.

## 24. Calculate the Ages According to the Birth Dates

If you are a HR who wants to record the ages of all the employees, and the only data you have are the birthdates of them, you can of course figure out their ages by your own.

But if there are so much data that you can’t process all manually, is it possible to **calculate their ages using functions automatically**?

To save your precious time on this problem, there’s a powerful formula you should know.

Create a new column called “Ages” and input **=DATEDIF(A2,TODAY(),”y”) **in C2.

You can then see their ages after hitting **[Enter]** key and applying this formula to all cells in column C.

## 25. Protect Excel Files with Password

An Excel spreadsheet might contain some important information that you don’t want others to view. To prevent it from being peeked or even tampered, you can **set a password to your Excel files**.

Go to ** File** tab and click

**in Info panel.**

*Protect Workbook*Select ** Encrypt with Password**, enter a password in the popping out window.

Re-enter it and click ** OK** to confirm the setting. Then you document is protected. Next time you must enter the right password to open it.

## 26. Conclusion

With these **tips for Microsoft Excel**, you’ll be sure to processing data better and quicker at work. There are so much more useful tips to learn, of course, as Excel is such a powerful tool. For example, you can find specific data in Excel using **VLOOKUP function**, get corresponding result according to specific criteria by **IF function**, using **shortcuts** in Excel to boost your work efficiency, etc.