Microsoft Excel is used in day to day life, right from businesses to students, everyone uses excel for their convenience. The fact that it is free and easy to use makes it popular among all the classes of humans.
Excel spreadsheet serves the purpose of storing data in the form of lists; this is why businesses use it for storing the day to day transactions, or monthly staff transactions and students uses it to prepare different projects or simply for storing the school/college schedule, study schedule, etc.
Excel is popular among everyone, yet we don’t quite know excel as deeply as we think we do. No offense to anyone who has read a book on Excel or studied it thoroughly, but there will always be certain things which you thought were not possible or existed in Microsoft Excel. And all those who have not read any book has been seeking the help of internet for decoding the situations, here are a few tricks you should learn to label yourself with the tag of spreadsheet pro:
1. Link Break and Text Wrapping:
It may seem impossible to break the line and wrap the text. By typing Alt+Enter, you can create a new line or break the line. If this shortcut doesn’t work, then simply go to Wrap Text option present under the Home Tab (review option) at the top of the program.
This will help you in wrapping text (the default setting is to align the text on the right side of the cell.) You can easily resize the row or even the entire column and the text re-wraps to fit.
a. Ctrl+Shift to Select:
Using the cursor to select the data or set of cells can be pretty exhausting. Instead of going with the cursor option, one easily use the shortcut Ctrl+Shift to select cells and information stored. Through this shortcut, you can select the cells above, below, right, left from the particular cell. Selecting cells according to direction is simple, use Ctrl+Shift+” the arrow as per direction.”
If you use Ctrl+Shift+End, then the cursor will jump (with the data) to the lowest right-hand cell, this means all the cells in between are selected. You can work faster by using Ctrl+Shift+*. This will select the entire data set no matter what cell is selected.
There are so many times when we are adding a number series or alphabets in the natural order but what if you were told that doing this via shortcut is possible.
For example, if you are adding a date in a cell and you will need to enter it for another 19 cells. Then simply enter one date (01/01/2019) and click on it. You will see a cursor (+). Keep dragging it for the next 19 cells and the cells will be filled automatically. You can drag the cursor in the direction of your choice.
c. Ctrl+Enter to add the same data in multiple cells:
Typing the data in each column is energy and time-consuming. Instead, use the shortcut Ctrl+ Enter to paste the same data in different columns. Click on the set of cells by dragging the cursor.
You can also do it with the help of Ctrl key, by holding it as you click each cell. Now, type the data in the last cell of the selected set then hit Ctrl+Enter; what you have entered into the last cell will be pasted in all the selected cells.
This feature will help you in filling the cell based on the pattern. This feature fills the column based on the pattern followed in the first cell (if the top row is the header row).
For example, if you have entered a phone number, which is formatted like “919876543210” and you wish them to look like “(91)-98765-43210”. Then type the format in the first cell, and by the time you will reach the second cell, the spreadsheet will recognize the pattern and display the suggestion. Now hit enter to use it. But if the hint is still missing then type out the format in the second cell after that go to Data Tab and click on the Flash Fill button.
The good news is that it works with everything; right from numbers, names to dates.
3. “Paste special” = conversion:
There are times when you want to convert the rows into columns or columns into rows. But you are blank on how to do it. Well, this is a no-brainer, and you can do it easily with the help of paste special. Follow the process below:
Copy the data you want to convert > Right-click > Select Paste Special > Check the Transpose Box > Click on OK to paste into a different direction.
Columns will become rows and rows will be converted into columns.
4. Charts to make it attractive:
You might be thinking that charts suit powerpoint presentation more than excel records, but you know what? Charts will make understanding easy. Also, charts are the easiest way to store the data.
There are many forms of charts and graphs in the excel spreadsheet. You can choose the one which suits your data best.
5. Working with the cells across sheet:
Also known as 3D Sum, will work if you have multiple sheets in the workbook and the basic layout used in all these sheets match each other. For example, each sheet consists of either early or quarterly statement, etc.; In the case, in cell D9, you have a certain amount for the same consequent week over time. Now you want to add all the amount and show it in a particular cell then what you can do is:
Go to a new workbook > Click on a cell > Type the formula for addition sum(‘Y1: Y10″!D9)
This will add all the cells titled “Y1 to Y10”. The result will be the sum of 10 years.
This can be used while preparing a master spreadsheet which consists of ever-changing data.
6. Go word or powerpoint from Excel:
Inserting the excel records in Microsoft word or powerpoint is a link-and-embed process. This means if you are taking the data in word or powerpoint, then you a change in excel will also change the data in word or powerpoint (wherever the data is copied). To avoid it from changing, you can follow the process below:
Go to Microsoft excel spreadsheet > Go to Home Tab at the top > Select the Copy Menu > Use the Copy as picture option.