MS Excel Custom Formatting - Show Units Of Measure Without Changing Your Numbers To Text

Without Changing Your Numbers To Text Show Units Of Measure in MS Excel with Example.

Many of you don't know that you can display a unit of measure (eg. lb., kg., km., in., etc...) in a cell of MS Excel worksheet without changing your numbers to text, so you can still use them in calculations?

It is not a very tough work, it's very easy! All you need to do is create a Custom Number Format.

To Add Measure Units in your MS Excel worksheet cell without changing the format of your cell from Number to Text follow the given below steps:-

1) Select the cells which containing the numbers;

2) On the Home tab, click the small dialog launcher arrow on the bottom right corner of the Number group;

3) In the Format Cells dialog on the Number tab click Custom in the Category list;

4) In the Type field, select a number format for your cells;

5) Type the unit of measure enclosed in quotes (e.g. " km." ) to the right of the number format in the Type field.

Format Cells as Number with Text (Measure Units) in Excel

Now when you look at the numbers in your MS Excel worksheet, they will display the unit of measure, but in the formula bar you can easily see that they are still numbers that you can use in calculations.

Read More

Hide Duplicate / Repeating Values in a Column in MS Excel.

How to hide Repeating /  Duplicate Values In A Column in Microsoft Excel with Example?


As you are working with lists in MS Excel, after sorting, you may find that, you have multiple rows with the same values repeating down one or more columns. If you are printing this list as a report, for aesthetic (pleasing) purposes, you may prefer to have only the first instance of each item shown.

One option is to clear the cells for all duplicate items below the first one. The problem with this approach:

1) approach can be very time-consuming if you're working with a long list and

2) once you delete values from the cells, you won't be able to sort or filter your list.

Here is a clean and easy trick using Conditional Formatting that you need only do one time. After that all future duplicate values will be hidden.

So, How to hide repeating values in a Column in Microsoft Excel (While Printing) with example?
1) Select a range of cells in the column where you want to hide duplicates;

2) In this example we have selected B2:B28, you will notice that cell B2 is the Active Cell;

3) On the Home tab, click Conditional Formatting in the Styles group and then choose New Rule...;

4) Select 'Use a formula to determine which cells to format';

5) In the formula field type =B2=B1

6) Click the Format... button, click the Font tab, select the Color dropdown, choose white* from the color palette and click OK.

Note:- * (Aestrik) This assumes the background of your cells is white so the duplicate text will be invisible.

7) Click OK to close the Conditional Formatting dialog.

You should now see that the only first value is visible but all the duplicates below are hidden. However they are still there (just look in the formula bar) but they blend into the white background.

Hide duplicates in Excel 2007 using this Conditional Formatting trick

Use Conditional Formatting and white font to hide duplicates in Excel 2007.

Read More

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