Home » Excel » Excel Tips you didn’t Know

Excel Tips you didn’t Know



Although decades old, Excel is still the topmost choice when it comes to spreadsheets and data entry. It is amongst the most powerful data visualization and analysis software available for both personal and professional use.

The software analyzes, stores, and tracks data with the help of a spreadsheet. As such, it is the first choice of marketers, analysts, and corporate workers. Since it is such a versatile tool, it is not impertinent that it has equal features.

These features are there to make the user’s life easier. But not every user is aware of these simple solutions. Hence, we have come up with the following article to help fellow Excel users. The article also includes tips and tricks to make your Excel journey seamless.

Pro Tips for a Smoother Excel experience

Excel Tips

Given below are a few tips that can help you out when using Excel, do give them a try.

Transpose Rows into Columns

The transpose function is useful when you want to copy a set of data from rows to columns. It is also helpful when you copy data from another source. Such data will always get pasted vertically in rows. Thus, users tend to spend too much time, rearranging them into columns manually.

Follow these steps to transpose rows into columns in Excel:

Step 1. Select the range of data.

Step 2. Press Ctrl+C to copy it.

Step 3. And then go to the desired cell.

Step 4. Finally, right-click and select Paste and then transpose.

Paste and Transpose Icon in Excel

Text Wrap

Re-sizing columns and rows is a necessity when it comes to data entry. Especially, when we want our presentation to look visually aesthetic. However, manually changing the shape of cells takes too much time. Thus, users are recommended to use the “Wrap Text” feature so that long strings of data can fit the entire cell without modification. This is usually located in the Home tab.

Wrap Text Button in Excel

Add Multiple Rows or Columns

Adding a single row or column is pretty simple in Excel. However, users can also add multiple rows or columns at once. And it is done by using the Insert feature. Select multiple rows or columns before inserting to add the same number of rows or columns instead of 1.

Half-fill a cell diagonally

Sometimes there is a need to differentiate cells with different looks. Although there are various methods to half-fill a cell, there is a simple remedy for this. And that is to use the Border feature. Users should first go to the menu bar to look up the Fonts group. And then select Borders. But don’t stop there. Underneath you can choose More options which allows you to add a diagonal line to a cell.

Border Icon in Excel

Deleting Blank Cell

Sometimes, during data entry, a few cells are left blank. Thus, for the sake of uniformity and accuracy, one should mark all these cells and delete them. Although manually selecting these cells can take a long time. Hence, here the user is recommended to use the Filter feature.

Here’s how:

Step 1. First, select the desired column.

Step 2. Go to the Ribbon.

Step 3. Then click on Find & Select > Go To Special.

Step 4. Now, select the Blank option.

Go To Special Blanks in Excel

Step 5. Finally, click Delete on your keyboard to remove all the cells in one go.

We all know that there is a search function that we can use by pressing Ctrl+F. But we can use two entries, Question Mark and Asterisk, to search the spreadsheet vaguely. It is used when the target result is unconfirmed. A Question Mark denotes a single character, and an Asterisk denotes more than one character.

Hide data from sight

It is also possible to hide data from plain sight. Just select the target cells, right-click, and choose Format cells. Then, on the number tab at the top, click on Category and choose Custom. Finally, type {;;;} and click on OK. Henceforth, the numbers will remain invisible, but you can still use them in formulas.


Although dragging a mouse and selecting a dataset appears fast, there are better ways to do so. All you need to do is click on the first cell of the required dataset and press Ctrl+Shift and then press the down arrow to select all the data in the row below, up to select all the data above, the right arrow to select the data in the column on the right side and left for the left side. Furthermore, if you press Ctrl+Shift+End, it will select everything from the starting cell to the rightmost cell with data. While Ctrl+Shift+* might be faster, it tends to stop at blank cells, which can be an issue.

Insert Screenshot

Inserting a screenshot in Excel is pretty simple. Just go to the Insert tab at the Menu bar and select Screenshot. A drop-down menu will appear that will list the thumbnails of all the ongoing programs. Choose one, and the image will get imported.

Performing Quick analysis

The Quick Analysis feature lists some of the popular choices in Excel, such as formatting, charts, sparklines, formulas, etc. When in a hurry, select the set of data and run Quick Analysis and then choose what to do with the dataset.

Same data in multiple cells

Sometimes a user is required to enter the same data at different places. But manually entering the same data over and over is not only repetitive but also boring. Hence, be smart about it.

  1. First, select the set of cells.
  2. Then write the information on the last cell.
  3. Finally, press Ctrl+Enter.
  4. The same data will appear in all the selected cells.

Here is a list of some of the popular shortcuts that can make your life easier.

  • Insert current date – Ctrl + ;
  • Insert current time – Ctrl + Shift + :
  • Change data format – Ctrl + Shift + #
  • Font strike-through – Ctrl + 5
  • Hide current column – Ctrl + 0
  • Hide current row – Ctrl + 9
  • Change Excel Workbook – Ctrl + F6
  • Show all Formula – Ctrl + `
  • Quick Scroll – Ctrl + PageUp/PageDown
  • Edit current cell – F2
  • Right-click prompt – Shift + F10

Use “&” to combine cells

Sometimes data is recorded under different multiple titles or headings. For example, a name could be recorded as a first name and a last name. But what if you require a single cell for the name? All you need to do is combine data under the First and Last name rows. This can be done by using the & sign in the formula. Suppose you want to join A2 and B2 then the formula would be:

=A2&” “&B2

Adding Hyperlinks is pretty simple in Excel just copy and paste it. But in case that doesn’t work, or you need to highlight a particular word as a hyperlink, you should use the Ctrl + K command.

The Format Painter function

As you may know, Excel is a feature-rich program that is equally good at mathematical operations. However, formatting cells manually is often a chore. If you have to apply the same formatting over and over to each and every cell on a different worksheet, then use the Format Painter feature.