Restore R1C1 Style Cell Referencing To Pivot Tables in Excel.

Reset R1C1 Style Cell Referencing To Pivot Tables in MS Excel Spreadsheet With Example.
 

When you refer to a cell in a Pivot Table in Microsoft Excel, you will see a GETPIVOTDATA formula instead of a normal R1C1 style cell reference by default (e.g. =D5) as shown in below image.

GetPivotData

You can easily turn off this GetPivotData feature, If you want to use normal cell referencing.
 

WARNING! A major disadvantage of using R1C1 cell referencing is R1C1-style references does not adjust if the Pivot Table layout changes.

How to enable RICI Referencing in MS Excel Pivto Table? or How to disable / turn off the GetPivotData Function in Excel?


To enable R1C1 referencing follow the below steps...

1) Click any cell in the PivotTable and from PivotTable Tools click the Options tab;

2) In the PivotTable group, click the small arrow on the right side of the Options command. You will see a checkmark next to the 'Generate GetPivotData' option;

3) Click the 'Generate GetPivotData' to turn the option off. Note that this is a global setting that will remain off for all PivotTables until you turn it on again.

GetPivotData option On / Off

Now you can reference data in PivotTables using normal R1C1-style referencing with the Generate GetPivotData option turned off.

How to turn the option GetPivotData back on in MS Excel Pivot Table?

R1C1 Option disable

To turn the option back on, click the 'Generate GetPivotData' option again.

Remember :- when you switch bewteen GETPIVOTDATA and R1C1, previously created references to the Pivot Table do not change.

Read More

Add a Descriptive Text to Number Formats in Excel with Example.

How to use Custom Number Formats / Combine Descriptive Text to Number Formats in MS Excel with Example / Add Text to Number Formats through Custom Format.



If you want to Combine or include descriptive Text to your Number Formats in Microsoft Excel Spreadsheet you can also use custom number formatting.

How to apply custom format to the numbers / Numeric Values or How to add a descriptive Text to Number Formats in Microsoft Excel Spreadsheet with Example?

 
To add descriptive text in your number format, write / type the text you want to display in double quotes within the number format code.

So instead of using a formula that I used in my previous post which is given below....

="Report due " & TEXT(A4,"dddd mmmm d, yyyy at h:mm am/pm")

 

 ...you can get the same result by applying this custom number format to the cell(s) containing the date/time or a number.

"Report due " dddd mmmm d, yyyy "at" h:mm am/pm

Custom Number Format

In the above example you will find that the additional text 'Report due' and 'at' are typed in double quotes within the number formatting code.

Note:- It is necessary that you type text within double quotes but you don't have to use double quotes around text that doesn't contain any of the characters that Microsoft Excel uses in its number format codes,  like 'm,d,y,h,m,s,e,@'.

To avoid any confusion or errors, it's better to ALWAYS use double quotes around descriptive text in number formatting.

One of the biggest advantages adding text to number formats is that the value in the cell(s) is not affected. You can still sort, filter, and reference the cells in your formulas.

Custom Number Format

Read More

Concatenate Text And Numbers From Different Cells in MS Excel 2007 or 2010 with Example

Concatenate Text And Numbers From Different Cells / Combine Text And Numbers From Different Cells in MS Excel 2007 and 2010 With example.

There are so many situations when need to combine values from different cells into one / single cell. For Example when you have a table of names where the first names are in one column (B) and the last names are in another column (A). You can CONCATENATE (or combine) the contents of these two separate columns into a string of text in another column (C) using a formula such as =B1 & " " & A1. Note the blank space added betyouen the first and last name. You could also use CONCATENATE function =CONCATENATE(B1," ",A1) and get the same result in Microsoft Excel. But the ampersand (&) method is much simpler.

Concatenate Text

Now, suppose you want to combine / Join (Concatenate) some text with a numeric value from another cell, for example, "Payment is due 21-Sep-2012". Lets suppose M5 contains the payment date, you can create a formula using ="Payment is due " & M5. You will notice, however, that the payment date displays as 41173. The reason is that the formula uses the underlying value (41173) from the referenced cell rather than the formatted value you see in cell M5 (21-Sep-2012).

Concatenate Text with Numbers

Normal numeric cell formatting does not work with text strings. To format the number in your text string, you need to include the TEXT function in your formula. The TEXT function allows you to define the formatting of a number in a text string. In this example, your final formula would be ="Payment is due " & TEXT(M5,"dd-mmm-yyyy").


Understanding this opens up a whole new range of possibilities. Below are some other examples of combining text and numbers in Excel. There are many, many more.

Some of the other ways you can use this in your spreadsheets?

Concatenate Text with Number

Read More

Remove Leading Apostrophes in MS Excel with Example.

Remove Apostrophes (') starting in a word or number in MS Excel / Easily Remove Leading Apostrophes with Example in Microsoft Excel.

How to Remove Quickly leading apostrophes in MS Excel?

Sometimes when you import data from some other source into MS Excel, you may find that some of the values (either text or numbers) contain leading apostrophes. These apostrophes doesn't look out normally but when you edit the cell then it looks out. These apostrophes creates an issues when you reference those cells with various formulas such as VLOOKUP. 

Remove Apostrophes

You can't remove that apostrophe with the help of simple Find and Replace (Search and Replace), you think that it would work, but unfortunately, that won't work.

So here is a trick / way to remove leading apostrophes. 

The quickest way I know of to remove leading apostrophes is by using the Format Painter (Home tab, Clipboard group) to copy the format from a cell without an apostrophe (e.g. a blank cell) to all of the cells containing the apostrophes.

If any of these cells contain leading zeros and you want to keep them, you will need to format those cells as Text.

Read More

AutoFit Rows for Merged Cells in Excel with Example

AutoFit Rows for Merged Cells in Microsoft Excel Spreadsheet or Worksheet with Example.

In MS Excel Worksheet / spreadsheet, when you enter text that is too wide for a column, the row height automatically adjusts to display all of the text wrapped on multiple lines in that cell if the cell is formatted to wrap text.

AutoFit Cells in Excel

However, if you merge cells in one row and format them to wrap text, Excel doesn't automatically adjust the row height. In this case, when you AutoFit the row height, Excel will shrink the cells to one row high.

You must manually adjust the row's height to display all of your text in the merged cells. If the text in these merged cells is constantly being changed, this can be a somewhat of an annoyance.

To understand this lets take an example below…..

1) In a new workbook, select cell C3 and, on the Home tab, click the Wrap Text button;

2) Type a long string of text in the cell and press Enter. As you can see, Excel automatically adjusts the row height so that all of the text is displayed in the cell;

3) Now, select cells C3 and D3 and merge them.

NOTE:- To merge two or more cells in Excel Go to the Home tab, click the dropdown arrow on the Merge & Center button in the Alignment group and select Merge Across;

4) Try to AutoFit the row height by double-clicking the bottom of the row heading. Instead of adjusting the row height to accommodate the wrapped text, Excel adjusts the cells to one row high, thereby hiding some of your text.

AutoFit Cells in Excel

To display all of the text within the merged cells, you will need to manually adjust the row height unless, of course, you know a technique that tricks Excel into automatically adjusting row heights for merged cells:

Here is also a trick, take a look…

1)  In a column to the right of your spreadsheet data and outside of your print area (e.g. column F), enter the formula =C3 and, if your worksheet has more cells that you want to AutoFit, copy it down to the last row that has data in column C;

2) Next, adjust the width of column F to be approximately equal to the width of your merged cells. For example, if column C width is 14.23 and column E width is 10.34, adjust column F to be 24.57;

3) Click the column F heading, then click the Wrap Text button on the Home tab;

4) Next, with column F still highlighted, on the Home tab, click the small arrow below the Format button in the Cells group and click AutoFit Row Height and you'll see that the row heights are automatically adjusted to accommodate the text in the merged cells.

AutoFit Cells in Excel

NOTE:- Note that if you manually adjust a row's height, Excel's AutoFit (as you enter data) feature will not work on that row. To reset the AutoFit feature, double-click the bottom of the row heading or on the Home tab, in the Cells group, click Format, AutoFit Row Height.

Read More

Hiding Unused Rows And Columns in Microsoft Excel Worksheet

Hiding Unused Rows And Columns in Microsoft Excel Worksheet.



Some worksheets are small and some are large. Knowing how to quickly get around your worksheet can help you work faster, easy and with less frustration. But has this ever happened to you? You hold down the Page Down key to quickly move to the bottom of your 40-page worksheet only to find that you scrolled too far past the end of your data?

Maybe you sometimes press the CTRL+END keys to go to the bottom-right corner of your worksheet but find that there are many blank rows below and blank columns to the right of your original data. Then you have to scroll back to your data.

Maybe you have discovered that holding down the CTRL key and pressing one of the arrow keys is a quick way to get to the end of a long range of data or, if you are starting from a blank cell, to the first non-blank cell in that direction. Have you evere pressed CTRL+right arrow and arrived at last column (column XFD)? Or pressed CTRL+down arrow and found yourself at last row (row 1,048,576)?

Pressing CTRL+END would take you to  the 'real' bottom-right corner of your worksheet data. Knowing how to go around Excel at a very fast speed like light is great but when you 'zoom past' your intended destination...it can get frustrating.

So, Here is the question, what can you do about it?
Well, here's an option some peoples may not have heard of.

Have you ever thinked why your worksheets have to be so big when they usually contain such a small amount of data?

Every excel user know how to hide rows and columns in MS Excel, but what if you could hide ALL of the unused columns to the right of your data and ALL the unused rows below your data?

What would that look like? You may be very surprised.

Hide Unused Rows N Columns

Limit access to only the visible area of your Microsoft Excel worksheet.
It may not be obvious how to hide all columns or rows beyond the range of cells that you are using, so here's how to do it...

To hide all columns to the right of column M, click the column N heading, hold down the CTRL and SHIFT keys and press the right arrow. This assumes that all cells to the right of the Active Cell (N1 in below example) are blank. You should now have all columns from N to the end of your worksheet selected. Right-click anywhere within the selected range and choose Hide.

To hide all rows below row 50, click the row 51 heading, hold down the CTRL and SHIFT keys and press the down arrow. This assumes that all cells below the Active Cell (A51 in this example) are blank. You should now have all rows from 51 to the end of your worksheet selected. Right-click anywhere within the selected range and choose Hide.


When you want to unhide the columns you have hidden to the right of your data, click the last column heading and drag your mouse to the right, release the mouse button, right-click the column heading and choose Unhide.

When you want to unhide the rows you have hidden below your data, click the last row heading and drag your mouse down, release the mouse button, right-click the row heading and choose Unhide.

You may not want to do this with all of your workbooks, but there may be advantages to doing it with some of them.

What Are the Advantages of Limiting The Visible Area of Your Worksheet?
Limiting the visible area of a woksheet...

1) ...allows you to use the CTRL+<arrow keys> and CTRL+SHIFT+<arrow keys> without accidentally zipping at warp speed to the far ends of your worksheet.

2) ...forces inexperienced (and experienced) Excel users to stay within the work area.

3) ...prevents accidentally creation of a 'last-used cell' far beyond your actual data, that can sometimes lead to printing problems and can unnecessarily increase the size of your workbook.

4) ...makes it look cleaner and focuses the users attention on the most relevant information.

5) ...makes it easier to set and reset the Print Area.

Be careful about hiding formulas and other data in this hidden area because, unless the workbook is protected, users may inadvertently delete the rows or columns that include the hidden information.

Read More

Determine the Nth Day of Each Month in MS Excel.

How to Determine The Nth day Of Each Month / Excel formula to know the Nth day of Each Month in MS Excel.



Suppose you need to create a meeting schedule for the 2nd (Second) Wednesday of each month, then will you complete your task in Microsoft Excel.

To Complete this task you can use the following formula to come up with the dates.


The formula requires 4 input values.

A1 = Nth
A2 = Nday of the week (i.e. Sun=1, Mon=2 ... Sat=7)
A3 = Month
A4 = Year

 =DATE(A4,A3,1+((A1-(A2>=WEEKDAY(DATE(A4,A3,1))))*7)+(A2-WEEKDAY(DATE(A4,A3,1))))

Nth Day of Each Month

Read More

MS Windows (Microsoft Windows) most used (important) keyboard Shortcuts

Must know windows keyboard shortcuts / Most Important keyboard shortcut for microsoft windows / More than 100 Keyboard Shortcuts MS Windows must read.

To increase the productivity or to increase the output of your work, every people who use computer in his / her professional or personal life should know or should have knowledge of important MS Windows keyboard shortcuts. By knowing these shortcuts a person can improve his/her out and do work fast. So read the following microsoft windows shortcuts:-

Keyboard Shortcuts (Microsoft Windows)

1. CTRL+C (Copy)
2. CTRL+X (Cut)
3. CTRL+V (Paste)
4. CTRL+Z (Undo)
5. DELETE (Delete)
6. SHIFT+DELETE (Delete the selected item permanently without placing the item in the Recycle Bin)
7. CTRL while dragging an item (Copy the selected item)
8. CTRL+SHIFT while dragging an item (Create a shortcut to the selected item)
9. F2 key (Rename the selected item)
10. CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word)
11. CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word)
12. CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph)
13. CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph)
14. CTRL+SHIFT with any of the arrow keys (Highlight a block of text) SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document)
15. CTRL+A (Select all)
16. F3 key (Search for a file or a folder)
17. ALT+ENTER (View the properties for the selected item)
18. ALT+F4 (Close the active item, or quit the active program)

MS Windows Keyboard

19. ALT+ENTER (Display the properties of the selected object)
20. ALT+SPACEBAR (Open the shortcut menu for the active window)
21. CTRL+F4 (Close the active document in programs that enable you to have multiple documents open simultaneously)
22. ALT+TAB (Switch between the open items)
23. ALT+ESC (Cycle through items in the order that they had been opened)
24. F6 key (Cycle through the screen elements in a window or on the desktop)
25. F4 key (Display the Address bar list in My Computer or Windows Explorer)
26. SHIFT+F10 (Display the shortcut menu for the selected item)
27. ALT+SPACEBAR (Display the System menu for the active window)
28. CTRL+ESC (Display the Start menu)
29. ALT+Underlined letter in a menu name (Display the corresponding menu) Underlined letter in a command name on an open menu (Perform the corresponding command)
30. F10 key (Activate the menu bar in the active program)
31. RIGHT ARROW (Open the next menu to the right, or open a submenu)
32. LEFT ARROW (Open the next menu to the left, or close a submenu)
33. F5 key (Update the active window)
34. BACKSPACE (View the folder onelevel up in My Computer or Windows Explorer)
35. ESC (Cancel the current task)
36. SHIFT when you insert a CD-ROMinto the CD-ROM drive (Prevent the CD-ROM from automatically playing)

Microsoft Natural Keyboard Shortcuts

1. Windows Logo (Display or hide the Start menu)
2. Windows Logo+BREAK (Display the System Properties dialog box)
3. Windows Logo+D (Display the desktop)
4. Windows Logo+M (Minimize all of the windows)
5. Windows Logo+SHIFT+M (Restore the minimized Windows)
6. Windows Logo+E (Open My Computer)
7. Windows Logo+F (Search for a file or a folder)
8. CTRL+Windows Logo+F (Search for computers)
9. Windows Logo+F1 (Display Windows Help)
10. Windows Logo+ L (Lock the keyboard)
11. Windows Logo+R (Open the Run dialog box)
12. Windows Logo+U (Open Utility Manager)
13. Accessibility Keyboard Shortcuts
14. Right SHIFT for eight seconds (Switch Filter Keys either on or off)
15. Left ALT+left SHIFT+PRINT SCREEN (Switch High Contrast either on or off)
16. Left ALT+left SHIFT+NUM LOCK (Switch the MouseKeys either on or off)
17. SHIFT five times (Switch the StickyKeys either on or off)
18. NUM LOCK for five seconds (Switch the ToggleKeys either on or off)
19. Windows Logo +U (Open Utility Manager)
20. Windows Explorer Keyboard Shortcuts
21. END (Display the bottom of the active window)
22. HOME (Display the top of the active window)
23. NUM LOCK+Asterisk sign (*) (Display all of the subfolders that are under the selected folder)
24. NUM LOCK+Plus sign (+) (Display the contents of the selected folder)

Remote Desktop Connection Navigation

1. CTRL+ALT+END (Open the Microsoft Windows NT Security dialog box)
2. ALT+PAGE UP (Switch between programs from left to right)
3. ALT+PAGE DOWN (Switch between programs from right to left)
4. ALT+INSERT (Cycle through the programs in most recently used order)
5. ALT+HOME (Display the Start menu)
6. CTRL+ALT+BREAK (Switch the client computer between a window and a full screen)
7. ALT+DELETE (Display the Windows menu)
8. CTRL+ALT+Minus sign (-) (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)
9. CTRL+ALT+Plus sign (+) (Place asnapshot of the entire client window area on the Terminal server clipboardand provide the same functionality aspressing ALT+PRINT SCREEN on a local computer.)

Microsoft Internet Explorer Keyboard Shortcuts

1. CTRL+B (Open the Organize Favorites dialog box)
2. CTRL+E (Open the Search bar)
3. CTRL+F (Start the Find utility)
4. CTRL+H (Open the History bar)
5. CTRL+I (Open the Favorites bar)
6. CTRL+L (Open the Open dialog box)
7. CTRL+N (Start another instance of the browser with the same Web address)
8. CTRL+O (Open the Open dialog box,the same as CTRL+L)
9. CTRL+P (Open the Print dialog box)
10. CTRL+R (Update the current Web page)
11. CTRL+W (Close the current window)

Dialog Box - Keyboard Shortcuts

1. CTRL+TAB (Move forward through the tabs)
2. CTRL+SHIFT+TAB (Move backward through the tabs)
3. TAB (Move forward through the options)
4. SHIFT+TAB (Move backward through the options)
5. ALT+Underlined letter (Perform the corresponding command or select the corresponding option)
6. ENTER (Perform the command for the active option or button)
7. SPACEBAR (Select or clear the check box if the active option is a check box)
8. Arrow keys (Select a button if the active option is a group of option buttons)
9. F1 key (Display Help)
10. F4 key (Display the items in the active list)
11. BACKSPACE (Open a folder one level up if a folder is selected in the Save As or Open dialog box)

MMC Console keyboard shortcuts

1. SHIFT+F10 (Display the Action shortcut menu for the selected item)
2. F1 key (Open the Help topic, if any, for the selected item)
3. F5 key (Update the content of all console windows)
4. CTRL+F10 (Maximize the active console window)
5. CTRL+F5 (Restore the active console window)
6. ALT+ENTER (Display the Properties dialog box, if any, for theselected item)
7. F2 key (Rename the selected item)
8. CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)

Read More