Use Line Breaks In Complex Formulas To Make Them Easier To Read in MS Excel with Example

How to break complex/Long Formulas into multiple lines to make them easier to Read in Microsoft Excel with Example.

Everyone know that in Excel, he/she can get very creative with formulas and do things most users would never have thought possible. There is one drawbacks with long, complex formulas that they are often difficult to understand - especially when a person go back and look at them long after they were originally created.

Break Split Formulas into multiple lines

Many people don't know this but you can make complex, long formulas easier to read by breaking them into smaller parts on separate lines. You can insert line breaks in your Formulas & Functions- just like you can insert line breaks in text in a cell.

Breaks Excel Formulas into Lines

To break the next part of the formula to a new line just simply press ALT + ENTER as you are

editing your formula. (If you're using a Mac, hold down the Option and Command keys while pressing Return).

Remember that the Formula Bar doesn't automatically adjust it's height to display formulas on two or more lines. To view your formula you can resize the Formula Bar. Just position your mouse pointer over the bottom edge of the Formula Bar and drag it down to see all of your formula. You'll also need to drag it back when you are finished.

Not only does this make the process of creating multi-step formulas easier, but also when you or someone else are viewing the formulas later, this will make them much easier to read and edit.

Read More

About Lotus Temple / Bahai Temple New Delhi

Lotus Temple / Bahai Temple New Delhi (Must Visit / See places in Delhi, India)

In the attraction places of New Delhi India, Lotus temple is one them. Lotus temple has an awesome architecture and great historical importance. Everyone wants to know the history behind it. So, what is the historical importance of lotus temple and awesome architecture of it.

Lotus Temple Night View

BRIEF HISTORY OF LOTUS TEMPLE / ABOUT LOTUS TEMPLE NEW DELHI (INDIA):-

    The Bahai Temple also known as Lotus Temple for its distinct half-open lotus design which is situated in New Delhi, one of the most visited buildings in the world, attracting over 50 million people since it opened in 1986.

Why Bahai Temple is named Lotus Temple?

Many of people are saying that this temple looks like a lotus in shape and because of it is known as lotus temple. But this is not the complete truth. Lotus is a symbol of love and purity. It always gives the message of immortality. This is the main cause of this Bahai temple is designed like a lotus flower and it is known as Lotus Temple.

Lotus Temple Front View

         Lotus Temple is famous in world for its architecture. Persian architect Fariborz Sahba built this beautiful awesome architecture who came from Canada. So this is the brief history of Lotus Temple / Bahai Temple.

ARCHITECTURE OF LOTUS TEMPLE:-

           To construction the lotus temple Marble, cement, dolomite, and sand were used in. If a person look this temple from the top side view it looks like half opened Lotus Flower. Construction of this architecture takes 10 years to complete. There were 800 peoples who have worked to construction Lotus Temple. In which engineers, technicians, and workers are included. Around the blooming petals there are nine pools of water, which light up, in natural light. It looks spectacular at dusk when it is flood lit.. White marble is used in the construction of Bahai temple and by which the beauty of Lotus temple is increased. Lotus Temple is 40 meters in Height.

Lotus Temple View From Top

Important Details about place, Opening Timing (Hours), Opening Days:-

Lotus Temple Location: Near Kalkaji Temple, East of Nehru Place New Delhi

Nearest Metro Station: Kalkaji Mandir Metro Station

Open: Tue-Sun; Mondays closed

Timings: 9am to 5:30pm

Entry: Free

Photography: Permission required

How to Reach Lotus Temple New Delhi, India:-

To Know the directions to reach to the Lotus Temple Click Here.

Direction to Reach Lotus Temple

Read More

Show or Display Dashes instead of Zeros in MS Excel Spreadsheet.

Display Dashes (-) Instead Of Zeros (0) in MS Excel Worksheet with Example.


Sometimes we may want our report to display dashes instead of zeros, especially those Excel spreadsheets that contain a lot of zeros.

Display zeros as dashes in Excel 2007

We can do this with a simple change to our current number format.

To do this simple change follow the given below steps:-

1) Select the cells where We want to apply the format;

2) On the Home tab in the Number group, click the dropdown and select More Number Formats... This will display the Format Cells dialog;

3) On the Number tab select Custom in the Category list. If all of the cells We have selected have the same number format, that format will be displayed in the Type field (e.g. #,##0;-#,##0)

Excel number formats can contain up to 4 sections of code separated by semicolons. The first section applies to positive numbers, the second to negative numbers, the third to zeros, and the fourth to text. So to display dashes instead of zeros, we have to edit the third section (zeros).

By default, most number formats consist of only two parts: a format code for positive numbers and a format code for negative numbers. When there is no code defined for zeros, the positive number format is applied for zero value cells.

4) To specify a format for zeros other than the default format, we must add (or change) the third section of the number format code. If there is no format for zeros we need to add a semi-colon for the third section and then type a dash. This tells Excel to display a dash in any cell with a value of zero. If We leave the third section (i.e. to the right of the 2nd semi-colon),  instead of displaying zeros, the zero-value cells will be blank.

Display zeros as dashes in Microsoft Excel 2007, 2010, 2013

Optionally, We can type one or more blank spaces following the dash to force Excel to offset the dash from the right edge of the cell by the width of the space(s);

Microsoft Excel 2007, 2010, 2013 Add space to right of dash

Read More

Unhide A single Hidden Rown or Column in MS Excel with Example.

How to Unhide A Single Hidden Row Or Column in Excel Worksheet with Example.

There is question here that is there an easy way or way to unhide a single column when multiple columns are hidden?

The Answer is Yes, there is an easy way.

If an excel user have a multiple rows or columns hidden and he/she needs to unhide just one. Some people unhide all hidden columns or rows and then re-hide all except the one they want. So there is no need to adopt such a long method, Here is an easier way...

To unhide a single column:-

1) In the Name box*, type a cell reference (e.g. D5) for any cell in the hidden column and press Enter. This will select that cell even though it is in a hidden column;

*(the white space to the left of Formula Bar)

Unhide a single row or column in Excel

2) Now from the Home tab, click the Format command, point to Hide & Unhide, then Unhide Columns.

To unhide multiple hidden columns:-

In Step 1 above, type a reference to the columns in the Name box.For contiguous columns, enter the top left and bottom right cells separated by a colon (e.g.. D5:E5).

For non-contiguous columns, enter a cell from each column you want to unhide separated by a comma (e.g. B5,G5).



Related Keyboard shortcuts:-

F5 opens the Go to dialog (can be used instead of Name Box to enter cell reference)

CTRL+9 to hide selected rows

CTRL+0 to hide selected columns

CTRL+Shift+( to unhide selected rows

CTRL+Shift+) to unhide selected columns

NOTE:- This Shortcut doesn't work in Excel 2013

Read More

Print Files directly from File Explorer.

How to Print Files Directly From File Explorer (#A.K.A. Windows Explorer) / How to Print Files without opening it?

#Also Know As (A.K.A or AKA)

Did you know that you don't have to open a file to print it?
There is a hidden trick by which you can save a significant amount of time.

There is a quick little known trick that lets you to print a document directly from File Explorer (formerly known as Windows Explorer prior to Windows 7) without having to first open its associated program first.

What you need to just simply right-click on the file in File Explorer and select Print from the popup menu.

Lets say you have to print reports for all 50 sales reports in your company. Rather than open each spreadsheet separately and then print, you simply go to File Explorer, hold down the CTRL key while selecting each file you want to print. Right-click and click Print.
 
There can be a question in your mind that how it works?

The way this works is, Windows opens the application associated with each file, prints the file(s), and then immediately closes (usually) the application.

Print Files Directly from Window Explorer.

Note:- If you select multiple file formats / types (e.g. Excel and PDF) at the same time, the Print option will not appear on the right-click menu. So select only same format files or same file types.

Read More

Use of SUBSTITUTE function/Formula with Example in Microsoft Excel.

Use of SUBSTITUTE function/Formula with Example in Microsoft Excel.



SUBSTITUTE is a great function/Formula in MS Excel that you may not have aware of, but now that you are about to learn, you will probably find everyday uses for it in Data Analysis.

In Microsoft Excel, you use the SUBSTITUTE function/Formula when you wish to replace specific text in a text string.

There are three pieces of information that are required for the SUBSTITUTE function/Formula in which a fourth is optional.

SUBSTITUTE SYNTAX:-

SUBSTITUTE(text,old_text, new_text, instance_num)
 

Text is the text string or cell containing text for which you want to substitute characters.
 

Old_text is the text you want to replace.
 

New_text is the text you want to replace it with.
 

Instance_num [Optional] specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Examples of SUBSTITUTE Function:-

Example 1:
Replace the 2nd occurrence of - (hyphen) with / (Slash)

Formula:   =SUBSTITUTE("ABC-123-456-789","-","/",2)
Result:      ABC-123/456-789

Example 2:
In "ABC-123-456-789" replace all occurrences of - (hyphen) with / (Slash)

Formula:   =SUBSTITUTE("ABC-123-456-789","-","/")
Result:      ABC/123/456/789

Because the instance number (i.e. 4th piece of information) is omitted from the function/Formula, Excel replaces every occurrence.

Example 3:
Something that you may need to do is replace text with nothing. In that case, use two double quotes (e.g. "") in the 'with this text' section. I frequently need to do this when I import account numbers, that include dashes, from my accounting system but need to use them without the dashes in Excel.

Formula:   =SUBSTITUTE("T-01-02-740-65","-","")
Result:      T010274065

You could also accomplish this with Find & Replace but you would need to do this every time the data is updated. Also, there will be times when you don't want to affect the original cells.

Substitute Function Excel

Example 4:
As with most other things in Excel, you can use cell references to supply information to SUBSTITUTE function/Formula.

In the previous example if your account numbers are in column B starting at row 10, you can enter =SUBSTITUTE(B10,"-","") in cell C10 and copy the formula down the column.

This is just a small taste of how the SUBSTITUTE function/Formula can be used for everyday applications.

Like most of these tips, the more you practice using this, the more ways you will think of to use it.

Read More

How to Freeze Panes for Wide Spreadsheets in MS Excel?

An Alternative trick To Freeze Panes For Wide Spreadsheets.

When using wide spreadsheets, you will often need to work with data on the far right of the sheet but also keep visible the related data on the left. You may already know that a good solution for this is Freeze Panes. With Freeze Panes, you select the column to the right of the one you want to freeze and, from the View tab, click Freeze Panes, Freeze Panes. Now you can scroll to the right while the frozen column remains visible on the left.

Freeze Panes is a good solution but is not without its drawbacks.

We often find that when we are moving around on the right side of my worksheet using the arrow keys, we sometimes scroll too far to the left and our data on the right scrolls off the screen. Then we need to scroll to the right again. This can get aggravating when it happens over and over.

Freeze Panes

One simple solution / way is to temporarily hide the columns that we are not using.

Another solution that works great for worksheets where we regularly need to hide (and unhide) specific columns is to use Grouping. Grouping is normally used for hiding the details of subtotaled data but, in some cases, it can work great for quickly hiding and unhiding columns.

This would also work for hiding and unhiding specific rows but in most cases.

How to hide specific column(s) using Grouping in MS Excel.

**Note that if you want to hide multiple columns, the columns for each grouping must be contiguous...

1) Select the (contiguous) columns (e.g. Columns D:Q) you want to apply Grouping to;

2) From the Data tab click Group. An outline bar and [-] symbol appears above the columns. When you click the [-] symbol, the columns will be hidden and the symbol changes to a [+];

3) You can select other (contiguous) columns to apply grouping to.

Once you have finished grouping your columns, you will have a simple and quick way to quickly hide and unhide these columns whenever you need to.

To permanently remove the Grouping (and outline symbols) from a column or group of columns, select the columns and, from the Data tab, click Ungroup.

Read More

How to Change Default Open/Save To Location of Excel with Example.

Change Excel's default Save to/Open Location.

If you want to put a specific location to Open or Save your Excel spreadsheet/File, you can do it which is very simple. There is a way to change the default location you get when you open or save workbooks in Excel. In the corporate world / Professional field or work, almost every people from us save our workbooks to a network drive rather then the My Documents folder on our PC's hard drive. So, if anyone would like to save his / her excel files on a network drive or somewhere other than the default My Documents folder, here is a solution in less than a minute.

When a person start Excel and open a file, by default, Excel first looks in the your My Documents folder. If he / she store most of your files in a different folder, they have to browse to it each time you open a file. When he / she save a new file or an existing file, Excel again defaults to your My Documents folder.

Now-a-days, so many of us are working on corporate networks that, more often than not, we open our files and save our files to a network folder. If you would like to change the folder location that Excel defaults to, there is a simple solution which very quick also.

1) In Excel 2010, from the File tab click Options. In Excel 2007, click the Office Button and then click Excel Options;

2) In the Excel Options dialog, click Save (on the left side) and look for the 'Default file location' field in the 'Save workbooks' section;

3) Type or paste the path of the folder you want to use as the default folder in the 'Default File Location' field;

4) Click OK.

Change Default Location

How to copy and paste a folder path in MS Windows?

If path of the new folder is very long, there's no need to type it all and take a chance on
misspelling it. Here's a trick you can use instead:

1) Open Windows Explorer, browse to and select the folder you want to use for your default Open/Save location;

2) Highlight the path in the Address Bar and press CTRL+C to copy it. (Note that Windows 7 doesn't show the path until you click in the blank area on the right end of the Address Bar. In Windows XP, if the Address Bar isn't displayed, from the View menu, select Toolbars, then Address Bar);

3) Switch back to Excel, click in the 'Default File Location' field and press CTRL+V to paste it.

Read More

How to Fix Wrongly Formatted Dates Quickly in MS Excel Spreadsheets with example.

Fix / Correct Wrong formatted Dates very quick in MS Excel. How to use Text to Column in Excel with Example.


Sometimes when we ever copy or import data into Excel from another source and we find that the date formats are all wrong. Take this example, your computer may recognize dates in mm/dd/yy format and you have just copied data from another source where the date format was dd/mm/yy.

So when you download, copy or insert data from another date format and open it in Excel, Excel doesn't recognize the dates. Here is an easy trick that allows you to quickly convert wrongly-formatted dates into a format you can work with.

In the given picture You'll notice that Excel recognizes some of the dates but for others it doesn't. Notice that the dates that Excel has recognized are right-aligned in the cells and those it didn't recognize, and assumed was text, are left-aligned. If you look closely you'll see that even the cells Excel did recognize as dates are sometimes wrong - the months and days are switched.

Date Format

It can be quite a task to fix all of these dates. There can be various techniques to solve this problem, but except this technique all of them are very time consuming. This is a simple little trick using Excel's 'Text to columns' option.

If you find yourself with a column of wrongly formatted dates Here's the method you can use...

1) Highlight the cells (only one column wide) containing the dates. You can select the entire column if you like;

2) On the Data tab click Text to Columns in the Data Tools group;

3) In the 'Convert Text to Columns' dialog, select the 'Fixed width' option and click the Next> button;

4) If there are any column break lines (vertical lines with arrows) through the data area, double-click each of them to remove them and then click the Next> button;

5) In the 'Column data format' section, you will see a dropdown next to the Date option, select the date format of the imported data (not the format you want to convert it to). So if your computer uses a month/day/year format (e.g. USA) and the dates you need to fix are displayed as day/month/year (e.g. UK/Canada), you will need to select the DMY format and Excel will convert them to the default date format of your computer. If your computer uses the dd/mm/yyyy format and you import US dates, you will have to choose MDY. Other formats such as YMD are also available if you import data that matches that format (e.g.20120531);

6) Click the Finish button.

Just like magic, Excel reads all of the imported MDY dates and converts them to DMY format (or DMY to MDY format). All of those dates have been fixed in just a few seconds.

Now, this one is the cool and fast way to do such a little but typical thing.

Read More

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