Advanced Excel Tips and Tricks for Windows users

Many of us are aware of some Excel tips but there are many more which we do not know. These lesser known advanced Excel tricks will help us to complete complex tasks in an easy way. It might be rounding off the value to thousands, changing the cell value which has impact on others and many more like this. This article will let you know how to use Excel tricks in such situations.

Advanced Excel Tips and Tricks

1. Monitor the data with Watch Window

When we are working on the long sheet, it is not so easy to identify how a change in cell value makes an impact on the other dependent cell. That would be more complex, if the dependent cell is not in the visible screen and it may be down somewhere.

We cannot keep on scrolling the sheet up and down for every cell change, right? Then Watch Window helps us to watch the dependent cell value. To do so, select the cell which you want to watch and then under Formulas tab select the “Watch Window”. It opens the dialog box and click on “Add Watch”. It shows the reference, check it once and click “Add”.

From now on, when you are changing the data, Watch Window shows the value changes of the dependent cell. Watch Windows keeps floating on the worksheet and you can even resize it. You can even watch cells on the other worksheets also.

2. Round Off the Values to Thousands and Millions

It does not look good having the long numbers in the cell and it looks weird. So, it is better to format them and show them in a simplified way. I mean showing thousands in terms of “K” and millions in terms of “M”. To do so, select the cell or range of value and right click on the selected region and select “Format Cells”.

Now, click on the “custom” and type ###,”k” for rounding off to thousand and ###, “m” for millions. Click “Ok” and you will see the simplified and rounded values. For example, value 22, 786.34 looks like 23k. This just changes the value looks and the actual value remains the same. So, there will be no problem in using this.

You can even format the axis shown in the chart in the same way. Select the axis you want to format and follow the above mentioned process.

3. Print Multiple Sheets on a single page

We will be in a situation where multiple worksheets should be printed on a single page. This can be done easily without the need of any add-on. For this, you need to have the camera on the Quick Access Toolbar (QAT). Click the down arrow of the QAT and select “More Commands”. From the dropdown “Choose commands from”, select “Commands not in the Ribbon”. Now, scroll down till you see “Camera”, select it and click on “Add >>” button, to add it to the QAT and click “Ok”. Now, camera got added to the Quick Access Toolbar.

Now, select the first region or area which you want to print and click on the camera icon. Open new worksheet and click where you want to paste it. You see that the selected region appears on the new worksheet.

Now, select the second region and do the same. Then follow this for all the regions you want to print on the single page. You need to remember one thing that, when you change the original value, then the value in the snapshot changes as it links to the original value.

4. Apply Table Format to your Excel Data

You can add the table style to your selected data. Select the data for which you want to add the table format and click “Format as Table” under Home tab. select the type of table you want, check the “My table has headers” and click “Ok” to see that table style is added to your Excel data. But, you also see that, filters are added to the columns and we want to get rid of it. For that, keeping the range selected, click on “Convert to Range”.

It asks “Do you want to convert the table to a normal range?” and click “Yes”. You will see that filters are removed and table format is applied to the data.

5. Create Custom List

It is always easy to select the value from the dropdown, rather than entering the value. You can create the custom data entry list in Excel to save the time of entering. For this, first have the values in one column which you want to the dropdown and select the location where you want to have the dropdown. Then, under “Data” tab click on Data Validation > Data Validation. It opens up the Data Validation dialog box, select “List” from the “Allow” dropdown. In “Source” filed, specify the range which has values you typed initially and click “Ok”.

Now, click on the cell for which you added the Data Validation and it shows the dropdown arrow. Click on that arrow and it shows all the in the list. Select the item and that makes it’s enter in that cell.

source:TWC