Excep Tips

Página 1

Change the shape of comment boxes in MS Excel

When you go to any cell in Excel and upon right click, you will be seeing the option called “Insert Comment”, by clicking this you can insert your comment for that particular cell.

Once you are done with the inserting of comment into the cell. Now, whenever you place the cursor on the cell, you could notice that the comment is becoming visible.

If you would like to reshape this comment box, right click on the cell and choose edit comment, then hold any corner of the box and drag to make it wider, taller to adjust the content.

But, sometimes you would like to even have different shapes altogether. So, in order to do this, we need to activate a feature that is not available in the ribbon menu system.

In the Quick Access Toolbar at the top of your screen you can right-click and choose Customize the Quick Access Toolbar.

Choose All Commands in the dialog box’s drop down menu. Let’s go to the C alphabet order and select the Change Shape feature and click on Add and then OK to add this feature to the quick access toolbar.

You can notice that the feature getting added in the top quick access toolbar.

Now, Go to the Cell containing the comment, click on edit comment, click on any border of the box and click on the change shape from the quick access toolbar to select the shape, now I am selecting the explosion shape and you can notice the shape getting changed. So, in this way one you can reshape the comment box as per your wish and requirement

So, this is all about changing the shape of comment boxes.

How to go to last Row/ last column with data in MS Excel/ How to select entire Row/ column in Excel.

In Excel, from any particular cell if you would like to move to last row, we generally press Ctrl + Down arrow and to move to last column, we press Ctrl + Right arrow. Similarly for first row and first column, we press Ctrl + up arrow & Ctrl+ left arrow respectively. This is all we knew til now.

But there’s a shortcut even for this?? Let’s see how it is now.

Go to any cell and from here if you would like to move to the last row, simply double click the bottom edge of the cell.

Similarly double click on right edge of the cell to move to last column. You can try with other two edges to move to the first row and first column.

Similarly to select the entire column, go to the first cell, hold shift and double click the bottom edge of the cell. You can do the same logic for selecting entire row as well.

Use of Flash Fill to quickly combine and separate column data in MS Excel

Flash fill is the most powerful tool that has been introduced in MS Excel 2013. Using this feature, we can combine multiple columns and separate the data from a column. Now let’s see with an example.

In the first column, I have Full names (First name separated by last name) and if I would like to reverse the order of first name and last name, we generally do that by using the Text to columns / concatenate functions. But using this new feature of flash fill, we don’t’ need to do this anymore.

In the adjacent column, I type the last name of the first entry, comma and type the first name and then go to the menu bar and go to the Data tab and click on flash fil (either u can press Ctl+E as a shortcut) and you will notice that the names populated as per your wish. It’s nice right.

In the 2nd example, let’s try combining the two columns. Now we have first name and last name in two separate columns and lets start by typing the first entry with last name , comma and first name and press Ctrl +E to flash fill the values.

In the 3rd example, let’s see how to separate the single column into multiple columns. In this column we have the City name, State Short name and pincode and let’s try to separate them in to three columns. For the first entry I type the city, state name and pincode in three columns separately and selecting these 3 cells together, when I go to the Flash fill feature in the data tab, you will notice that the feature is in inactive mode, but when you click on only first cell you can operate the flash fill now, repeat this for next two columns. It’s done right. You can even adjust the text formats using this flash fil feature.

So this is all about the Flash feature in Excel.

How to change the data without using the formula

In this example, I have some numbers 1 to 10 in this column and if I suppose to multiple each cell by 2, what would everyone generally do??.

Using formula in the next column, we multiple each cell by 2 but let’s see how we can do this without using the formula.

I type 2 in a cell and then copy the cell.(CTrl+C) now select the entire range which needs to be changed and then press CTRL+ALT+V and under operations select the multiply option and click OK, you will see the result getting exactly the way u wished.

Let us see one more example, here you will notice that using the formula is no better than using our new feature. Here I have targets for 4 regions by month wise and what if your boss asks you to be little aggressive and increase all the targets by 5.

Now simply type 5 in a cell and copy it and then select the entire range and press CTRL+ALT+V and choose Add under operations and click ok and you will see the values getting changed.

How To Display and Highlight All the Formulas

In order to see a formula in a cell, we generally double click a cell.

But sometimes, we may want to see all the formulas at once. To do that, there is a key stroke shortcut and also a menu command that will allow you to display all the formulas.

The key below the Esc key on your keyboard having the Tilde & accent grave can be used along with the Ctrl key as a key stroke shortcut now.

So, let’s try now by pressing the Ctrl+` key on your keyboard to see that all the formulas in the sheet, getting displayed and columns width has been increased.

If you press the Ctrl+` again you will be back to the normal display.

You can also find this feature from the menu bar, Go to the Formulas tab and select the option called show formulas

Now, let’s see the other trick of highlighting all the cells that contain formula

Go to the Menu bar and in the extreme right, you will see the Find & Select button, click it and choose formulas and then all cells that have formulas in this sheet are highlighted

Now you can apply color to all these selected cells as well, so as to remember them in an easier way.

Using this same trick of selecting all the cells containing formulas, I generally lock the cells and protect the workbook so as to make it more secure and standard reporting document.

Enter Data or formulas in multiple cells simultaneously

We know it is easy to copy and paste the data, but there is a technique to enter data in multiple cells so quickly.

Select the range of cells, where you would like to enter a value. Holding the CTRL key, click on any cell and then type the value as per your wish, say “DP”.

And don’t press enter now, instead press Ctrl+Enter and then you will get the entire series with the same name DP.

This works with the numbers as well, it doesn’t have to be in the same row or same column.

Let’s see, if I want to type 100 in these 4 cells, I simply select these 4 cells by pressing Ctrl key and selecting a cell of these and I type 100 and press Ctrl+Enter.

So, you can use this trick for numbers, text and also for formulas.

Let’s see an example for using the formulas as well.

Here, I have got some numbers and let us find the sum of all these columns. First, let me select all the cells here, type the formula as = Sum (select the range in the first column) and press Ctrl+Enter and you will see the row getting totals of columns in the table.

Accelerate Worksheet Copying and moving.

If we want to make a copy of any sheet, we generally right click and select move or copy. By doing this, we can move to other workbooks.

But here, if I want to have in this same sheet, hold down the ctrl key and start dragging the sheet away, you will see the + sign in it.

So if you move now towards the leftward or right side you will notice a little black pointer, if you release mouse first, we get a copy.

I'll do it again. Get the Control key held down drag to the right, and release the mouse to make a copy.

Página 2

How To Compare Format Of Two Cells In Microsoft Excel

http://www.exceltip.com/tips/how-to-compare-format-of-two-cellsin-microsoft-excel-2010.html

In this tutorial, let us learn how to compare the format of two cells in Excel

1st Example: In cell A5 and B5 let us put the date as 12/9/2018 i.e 12th Sep and now let us format A5 to display as 12/9/2018 and format B5 to display in a different format like 09/12/2018

that means we are keeping the format of cell A5 as dd-mm-yyyy and format of B5 as mm-dd-yy

and let us check the format in both the cells A5 and B5 same or not, using the formula as =CELL(“format”,A5)=CELL(“format”,B5) which gives the result as FALSE which is correct

2nd Example:

Now let us test with the other formats,

we have 27.11 in A6 and B6 but lets us show two decimals in A6 and only one decimal in B6, so the only difference being the decimal places.

Hence, both the cells contain the same value with different format.

Here, let us try with the same formula by dragging the cell C5 to C6, to see the result as FALSE again.

3d Example:

Now Cell A7 has value as 87.11 and cell B7 has 87.21

Though the values are different in both the cells but they are in the similar format i.e. both the Number format are showing 2 decimal places.

Here, we would like to see the result as TRUE because both the cells are of the same formats.

let us drag the formula to C7 now to see the output as TRUE.

So friends, to check the format of cells in excel, we should be using the formula called Cell

In this way, we can compare the formats of two cells in Excel. So, friends, I hope you have enjoyed this video tutorial.

Página 3

How to create Radio buttons in MS-Excel

http://www.exceltip.com/tips/how-to-create-radio-buttons.html

Options button or Radio button can be used in Excel to choose a particular option from some multiple options.

Let us consider an example that you are creating a survey and you require to create questions with 4 options to choose from.

You have to get the answer of the option that has been selected like this.

To create option buttons, first you need to go to Developer tab

To add developer tab like this to menu bar, you need to right click ribbon and select customize the ribbon option and then under choose commands from drop down menu, select all commands, click on Developer command and click on add button to add that control into the main tabs to right side and click ok.

Once the developer tab is added to your menu bar, go to Developer tab in the menu bar, under Controls group, click on Insert and from the “Form Control” click on option button and draw a rectangle on the sheet to insert the option button.

Copy the option button and paste it for 3 more times, so that we have the 4 choices now.

Change the name of every option button and name it as per requirement based on the question.

Let us put the question as "Which State you belong to?"

now put the options as "Maharashtra", "Andhra Pradesh", "Kerala", "Telangana"

Select any option button and right click and select format control

Format Object dialog box will open up, under control, In the cell link, select any cell, say I2 in the sheet and click OK.

By doing this, Whenever you select any option, linked cell number will get change according to the selection.

Enter the formula in cell B8, to check which option the user has selected.

=IF(I2=1,"Maharashtra",IF(I2=2,"Andhra Pradesh",IF(I2=3,"Kerala","Telangana")))

Press Enter

Now, whatever option you will select, it will show as an answer in this cell.

You can add more questions and format the page accordingly, to design surveys, dashboards etc.

So friends, in this way you can use the radio buttons in the MS excel.

Página 4

1. What-If Analysis:

http://www.tutorialized.com/tutorial/Excel-What-If-Analysis/77095

2. Force Text to Upper Case Automatically In Excel

http://www.tutorialized.com/tutorial/Force-Text-to-Upper-or-Proper-Case/59447

3. Name your Excel Workbook With The Text In A Cell

http://www.tutorialized.com/tutorial/Name-a-Workbook-with-the-Text-in-a-Cell/59370

4. Fill All Blank Cells in a List

https://sourcedaddy.com/ms-excel/filling-blanks-via-macro.html

5. How to transpose multi-column data into 3 columns

https://www.wallstreetmojo.com/category/excel/

6. How To Transpose / Convert A Single Column To Multiple Columns In Excel?

https://www.extendoffice.com/documents/excel/681-excel-change-columns-to-rows.html

7. MS Excel showing the formula in a cell instead of the result

http://users.wfu.edu/matthews/misc/Excel/FormulaDisplayBug.html

8. How to Arrange Worksheet tabs in Excel in an alphabetical order

https://excelribbon.tips.net/T013440_Alphabetizing_Worksheet_Tabs.html

9.Changing the Color of Worksheet Gridlines

https://excelribbon.tips.net/C0769_Worksheets.html

10. Automatically Renaming Worksheets

https://excelribbon.tips.net/T013572_Automatically_Renaming_Worksheets.html

11. if a cell contains specific text,

https://exceljet.net/formula/if-cell-contains

https://www.excelsuperstar.org/horizontal-sort-in-excel/

Snake Chart : https://www.goodly.co.in/vertical-line-chart-in-excel/

https://www.goodly.co.in/combining-list-values-single-row/

https://www.goodly.co.in/separate-numbers-from-text-excel-powerquery/

https://www.goodly.co.in/combine-duplicate-vlookup-values-single-row/

Group Pivot Table items in Excel

https://www.excelsuperstar.org/group-pivot-table/

Pie Chart

https://www.excelsuperstar.org/create-a-pie-chart-in-excel/

https://www.goodly.co.in/create-a-sheet-index-in-excel/

https://trumpexcel.com/excel-macro-examples/

Customer A have 7 invoices. I want to add under this 6 blank rows (or Duplicate complete row 7 times). Customer B have 15 invoices. I want to add under this 14 blank rows (or Duplicate complete row 14 times).

How to create Mekko and Sankey chartsHow to create Mekko and Sankey charts

Vlookup to Find Last Matching ValueVlookup to Find Last Matching Value

https://www.exceltip.com/excel-formula-and-function/vlookup-to-find-last-matching-value.htmlhttps://www.exceltip.com/excel-formula-and-function/vlookup-to-find-last-matching-value.html

Página 5

Name your Excel Workbook With The Text In A Cell

Script:

Hello Friends, Welcome to dptutorials

If you want to save your excel workbook using the text in a specific cell as a filename, you need to watch this tutorial.

Click on the cell B2, enter some text and save it, you can see the file name as same as cell B2.

Let us learn this trick now , how to Name a Workbook with the Text in a Cell

step1:

Right click on sheet1 tab and select the view code option or else press Alt+F11 as a shortcut to go to VBA window.

Step2:

Insert module and copy this code (I have given the code in the description)

Sub SaveAsCell( )

Dim strName As String

On Error GoTo InvalidName

strName = Sheet1.Range("B2")

ActiveWorkbook.SaveAs strName

Exit Sub

InvalidName: MsgBox "The text: " & strName & _

" is not a valid file name.", vbCritical, "dptutorials.com"

End Sub

Step3:

And paste it here in the VBA window

Step4:

Close this VBA window and go back to the Excel sheet.

Step5:

From the menu bar, go to view tab and under macros, click on view macros

Step6:

Select the SaveAsCell macro and click on Run

and your workbook will automatically be saved as cell B2 of Sheet1.

Try to change the name in the cell B2 and save it, you can see the name of the file keeps changing and saving as a separate workbook in the same folder

I hope this trick is very useful.

If you have enjoyed this tutorial, please do give me a like & share and also you can comment for improvement.

For further more interesting videos, please do subscribe dptutorials.

Thanks for watching.

Página 6

Automatically Change Worksheet Names Based On Cell Values

https://support.microsoft.com/en-in/help/2790229/step-by-step-rename-excel-sheet-with-cell-contents-by-using-macro

Script:

Hello Friends, Welcome to dptutorials

In some of our Excel reports, we may want to name the sheets automatically according to the names on each sheet without manually renaming them.

So, in this tutorial, let us learn how we can automatically change Worksheet names based on Cell values using a small macro.

In this example, I am considering a sample report of 6 states in 6 sheets and let us rename these sheets with that of States names from cell B2 of each sheet.

Now, let us follow the step-by-step procedure

Step1:

Right click on any sheet and select view code or press Alt+F11 as a shortcut

Step2:

Click on Insert from the menu bar, click on Module to insert a module

Copy the code (from description)

Sub RenameSheet()

Dim rs As Worksheet

For Each rs In Sheets

rs.Name = rs.Range("B2")

Next rs

End Sub

And paste in this new module.

Step3:

Close this VBA window and go back to your excel workbook

Step4:

Go to view tab and under macros, click on view macros

Step5:

After selecting the renamesheet macro, click on run

Step6:

Now, you can see that the name of each sheet is renamed in accordance with the cell name on each sheet.

That's it friends. I hope this is very use full for those who spend lot of time on Excel.

If you have enjoyed this tutorial, please do give me a like & share and also you can comment for improvement.

For further more interesting videos, please do subscribe dptutorials.

Thanks for watching.

Página 7

1. Remove password from excel without password | Unprotect Excel sheet without password Without Code

You might have seen how to unprotect an excel sheet using VBA code. But in this tutorial I am gonna show you how to unprotect excel sheet without password and without code.

Before starting the process, we need to enable the extension name display option for the files.

Go to any folder, click on organize, click on Folder and search options, go to view tab, untick the option "Hide extensions for known file types".

Step 1: Let us create an Excel sheet in this folder and lets open, add some sample entries and protect this sheet like this. you can check that this sheet is protected. Done. Close this sheet and Now let us see how to unprotect this sheet.

Step 2: Go to the folder containing the Excel workbook, select the workbook , right click and select rename option. change the extension name from “xlsx” to “zip”

Step 3: Double click the Zip file to open, Click on “XL” folder and then Click on the “WORKSHEETS” folder.

Step 4: Here, you will see one sheet with XML format, sheet1.xml is our protected sheet. right click on this sheet1 and click on extract to extract this file to the folder.

Step 5: Now, go back to the folder and right click on this extracted sheet.xml and open this with notepad

Step 6: Search for the word protection and remove this particular protection word from this notepad and save this notepad and close

Step 7: Now, select this sheet1.xml and drag this sheet into the zip file.

Step 8: Click ok to add to the zip file and close the zip file.

Step 9: Finally, rename this ZIP file back to .XLSX, click on Yes,.

Step 10: Open the file and now you can see that the sheet has been unprotected successfully.

I believe this trick would be really amazing, right. If you have enjoyed this tutorial please do like, share and comment.

Página 8

2. Making a text invisible in Excel with a magical trick.

If you want to make the text invisible, you would be generally making the font color white. This is a simple known trick. Anyone can see the text if the cells gets filled with some background color. so, this trick won't work all the time.

Now, let us dive into the actual trick.

Here we have the word invisible in this cell C3.

Step 1: Right click on this cell and click on format cells.

Step 2: Go to Number tab, select the custom formatting.

Step 3: Click on the general text here and type ;;;, click ok

Now, you will see that the text has gone invisible. you will see this text only in the formula bar and not in the cell C3.

Got it friends, this can be made visible by changing the format to general.