Excel is a part of Microsoft office and the world's widely used spreadsheet application. Compared to other spreadsheets Excel is very rich and popular. Excel will surely make you perfect in every aspect of your job. Here are some tips for you to know:
1. Enter Long text using the abbreviation method
You can use an auto-correct method for quickly company names in multiple parts of the sheets. If you want to type as XYZ Technologies Private Limited when you type XYZ use the method
Go to Excel options > Proofing and select autocorrect options. In the Autocorrect dialogue box type as XYZ and replace text with field type the full name as XYZ Technologies Private Limited. Now, if you want to type XYZ it will be replaced as the company name.
2. Joining text values in different cells
You can go with a concatenating method to combine different cells or else you can use the ampersand(&) to concatenate. For example, you have The in A2 cell, Excel in B2, and spreadsheet in C2. Now you can use =&A1&B1&C1 and you will get JohnDoeMeta.
If you want to add space between the words, modify the formula to =A2&" "&B2&" "&C2. From Excel 2016 you can use TEXTJOIN to combine different cell values and it's easy to use.
3. Hide/Unhide Rows
Sometimes, you may need to hide some rows or columns of data. This can be used for printing or for presentation purposes if there are many rows or columns. Right-click the rows or columns and select Hide. Shortcuts are Ctrl followed by 9 represents to hide the selected rows. Ctrl followed by 0 represents to hide selected columns.
4. Disable annoying formula errors
To disable annoying formula errors go to Excel options>Formulas> Error Checking rules and disable errors that don't want to be displayed.
5. Refreshing the Pivot Table
The Pivot table will not refresh automatically if fresh data is added. So you have to click refresh after selecting the Pivot table. If you need to update the Pivot table, automatically update the datasheet to the table. All new data will be refreshed automatically in the Pivot table. There is an option to refresh the Pivot on opening the file. For that select, the option, refresh data when opening the file under the data tab in Pivot Options.
6. Make a Chart quickly
To make a chart easier, Highlight data and press the shortcut key F11.
7. Enter Data in Multiple Sheets
If you want to enter formula or data in multiple sheets at once. First, select all the sheets, and then enter the formula or data. You will have the same details on all sheets. This would be useful if you are maintaining a monthly expense spreadsheet. The data will be different but if you are entering the same formula sheet and enter the formula in the first sheet, the same formula will be there in all sheets.
8. Repeating the Tasks
For example, now you have changed the color to Red. Now if you want to select any other cell or cells and use f4 that key color will change to Red. This shortcut intimates the previous tasks you have done. Do the task once and use the shortcut.
9. Insert a Dollar sign quickly
By using the F4 function key, you can quickly insert the dollar sign for changing the cell reference to mixed or absolute while entering the formulas. By pressing the F4 key you can change the reference. While using Vlookup or Index Match formulas it would be useful.
10. Import Data from Web
If you want to use any formatted HTML table from the web you can apply this method.
Go to Data Web>New Query>From Other sources>From Web in Excel > Enter the URL of the page and you will get the HTML tables on that page. For Excel 2010 or 2013 you need to install Power query add-in to use this feature and you will see the tab called power query. From this tab, you can click From Tab and click URL. If any data changes on the Web you can refresh the data by right-clicking the table.
