Excel Hacks

With More than 1.2 billion Microsoft Office users, Excel is perhaps the most versatile and widely used computer application in the world. It’s an indispensable tool for any business – used by students, managers, entrepreneurs, organizations, distributors, bloggers and even housewives to keep a track of their monthly expenditure.

Even with this much importance, there are some simple techniques that are undoubtedly overlooked and never used in practice. We have compiled a list of such powerful techniques that will not only make your complicated work – Simple but also increase your overall productivity, let’s dive in:

1. Select Entire Spreadsheet Columns or Rows 

• Select that particular cell of your sheet where you want to start from.
• Press CTRL +SHIFT with the arrow keys to select the data upwards, downwards or adjacent to the cell. You will get the required Row or Column selected.
• For selecting the entire Spreadsheet, Press CTRL+SHIFT+*.

Pro Tip: To Select rows or column one by one in a particular row or column. Use Shift + Required Arrow Key

2. Freeze Excel Rows and Columns

While Navigating through a lengthy spreadsheet, the data organized in rows and column gets lost once you scroll beyond a specific number. By using the Freeze pane option, you can fix any particular row or column.

• Select the particular cell of your spreadsheet you want to freeze.
• Click View from Menu options above.
• Then select Freeze Panes from the drop-down menu.

3. Enter Data Patterns immediately in Excel

You can automatically acknowledge Data Patterns in Excel and can also enter those patterns to the other cells.

• Enter the first two data of your pattern into the cells. For Example, if your data pattern is 2,4,6,8,….. and so on then enter only 2 and 4.
• Then, select both the cells and place your cursor where you see a small square.
• And start dragging it when you see a “black cross” sign. (Remember this function can be applied both horizontally and vertically.)

 4. Unveil Spreadsheet Formulas in Excel

If you are completely new to Excel and want to know what formulas are used in a specific sheet, then all you need to do is –

• Select Formulas from Menu options above.
• Then select Show Formulas or directly use a shortcut key CTRL+’.

5. Hide Spreadsheet Rows and Columns

It is quite possible that some of the details in your Spreadsheet can be just for your understanding and you may want to keep them hidden

• Select the row or column you wanna hide.
• Click Format from Home Menu option.
• From there select Hide & UnhideHide Rows or Hide Columns. (Remember you can hide the whole sheet from your Workbook by selecting Hide Sheet option under Hide & Unhide.)

6. Try adapting MS Excel Format painter

Everybody appreciates a well formatted sheet it allows a user to clearly follow your data and calculation. Format painter option makes it very easy when you want to copy formatting from a range of cells and paste it somewhere else in that sheet or any other Spreadsheet. MS Excel Format Painter copies Cell Color, Border and Number Formatting.

• Select the cells whose formatting you want to copy.
• Click Format Painter from Home
• Now select those cells where you want to copy this formatting.

7. Copying the same data into multiple cells

Sometimes the same data needs to be entered into multiple cells. Instead of doing this one by one using Ctrl- C and Ctrl-V you can follow these steps to achieve this in one go-

• By holding the CTRL button, select all those cells where you want to enter a similar text.
• Type the required text in the last selected cell and then press CTRL+ Enter. And the required text will be copied to all the selected cells.

 8. Remove Duplicate Data in Excel

If your Spreadsheet contains duplicate values and you want to remove them then go by these steps-

• Click on Data from Menu options.
• Then in the Data Tools section, click Remove Duplicates.
• Now a dialog box will appear on a screen. Select the required columns from where you want to remove duplicated data.

9. Transpose rows to columns and vice versa in Excel

Converting row to columns or columns to row is really easy, follow these steps:

• Select the data and Click the Copy
• Then instead of paste click Paste Special option wherever you wanna paste the data.
• Now one dialog box will appear on the screen, there select Transpose Your data will be pasted with different orientation now.

10. Split data of one cell into two different cells

Suppose you want to split data of any single row or column into multiple cells, follow these steps:

• First of all, separate the text or data, you wanna split using comma, semicolon or space.
• Then select that particular cell, row or column. Then Click the Data from menu options.
• In the Data Tools group, select Text to Columns.
• Now a dialog box will appear on screen, in this, select Delimited option, click next and select Comma, Semicolon or space whatever you have selected in the previous step.
• Again click next and keep the column data format at General Click finish. Data preview can also be seen there.

11. Integrate data of different cells into one cell

For combining cell data of different rows or columns together into one cell follow these steps-

• Select the cell in which you want to combine and put “=”
• Now click on the first cell whose data needs to be combined and then put “&” sign.
• Now click on the second cell and press Enter. (Remember for more no. of values, you only need to put & sign between them.)

 12. Use Data Validation Function in Spreadsheet

With this function, you can make sure that the data entered is correct according to your defined measures. The steps to use Data Validation Function are-

• Select the row or the column in which this function has to be applied. You can also select any particular cell.
• Now select Data from Menu Option, there select the Data Validation from a drop-down menu.
• Now a dialog box will appear on the screen, there select Settings and enter the type of value in the Allow option and fill other desired values accordingly.
• You can also write a message for display into Input Message beside Settings

There you have it – those were few excel tips to help you do more in less time. How many do you know? Let us know in the comment section below.