Formatting Text in Cells in MS Excel similar to MS Word!

 Introduction

     MS Excel is absolutely great for working with numbers but when it comes to formatting text in the Excel Cell, there is something which feels missing (or not directly accessible) in its text formatting capability especially if you are familiar with the MS Word. Although, the existing text formatting capabilities of MS Excel are enough in most circumstances but sometimes you do need something more for the sake of better presentation or readability.    

    In this article, I am going to share some cell formatting options available in Excel which you may find quite helpful. There are many folks who want to justify their text for symmetrical appearance. Some want their text to have MS Work like paragraph spacing. Some want to use Bullets or Indent text for enhanced readability. All these options are present in Excel to some extent placed under Cell Format Dialog Box and we will look at these options one by one.

Justifying your text in a Cell

     If you are having lots of text in a cell then you may want to Justify the text of that Cell for better appearance just like you do in MS Word. However, Excel is keeping this option under the Cell formatting dialog box. To Justify your text follow the steps as under:

  1. Select the range of Cells containing Text.

  2. Right Click and then Select Format Cells or Press Ctrl + 1 to open the Format Dialog Box.

  3. Select Alignment tab and then Select Justify under Horizontal Text Alignment.

  4. Click OK.

 


Using Bullets in Excel Cells

    There is no direct way to add bullets in Excel Cells the way you can do in MS Word. If you Google " How to add Bullets in Excel" , you may find myriad ways to add bullets. I prefer using the Combination of Alt Codes and Custom Number Formatting to add the bullets. 

Alt Codes: It's a way to enter special characters which do not have a dedicated key on your Keyboard. To enter a combination, you have to press the Alt Key and the code in your Numpad. For example, if you enter Alt + 0149 then it will display a solid bullet • . The are many combination and you can google to find the entire list, I am listing the most common ones below:

Alt + 16 Alt + 26 Alt + 0149 Alt + 175 Alt + 0187 Alt + 254
► → • ☼ » ■






Custom Formatting : Excel allows you to format the underlying value to suit your needs such as it may allow you to add currency symbol to numbers without changing actual value. For instance, suppose you are measuring the weight of some students and every time you measure the weight you put the value in Excel Cell but at the same time you also want to suffix "Kg" with each entry. If you type "Kg" with every entry then you are effectively changing the data type to Text and then you cannot perform any calculation such as average weight of the group. Here, the custom number formatting helps in allowing to suffix "Kg" to each entry without changing the underlying number. The following gif explains the process below:

 


Note: Hash (#) is used to  represent numbers in Custom Formatting in Excel.

Now with the some basic understanding of Custom Formatting and Alt Codes, we are ready to implement Bullets in Excel Cells. You may have already guessed that by simply placing your Alt Code Symbol before the Data Type will prefix the symbol automatically. 

The following gif gives an easy way for you to see how we can add bullets to Excel Cell:


Note 1: @ represents text in the Custom Formatting in Excel. If you want to add bullets to the numbers then simply replace @ with #. 

Note 2: You can use any other valid alt code for the bullets. Just press the Alt Key and press the the valid alt code. You can Google for the whole list of Alt Codes.


Inserting Indents in Excel Cells

   There is a default alignment in MS Excel for different data types. The Numerical Value if Right Aligned, The Text value is Left aligned and the Logical values are Center Aligned. 

It is always preferred to use the default alignment as it help identifying data types. If you are using default alignment then you may easily check for Numerical value (Right aligned) accidentally added as Text (Left aligned).

Using the default alignment may make the presentation of data a little less readable in some cases as shown below:

The above table uses the default alignment and you can immediately see the S.No. and Product are nestled together and same is the case with Price and Description thereby leaving a gap in the middle.

Here, you can insert Right Indents to 1st and 3rd Columns which will give better readability and also maintain the default alignment.

To insert the indents, follow the steps shown below:


Note: There is option for Left Indent also. If you want to Indent on both sides (Left and Right) then use Distributed Indent Option under the Horizontal Alignment Tab.


Adding Paragraph spacing like appearance to Excel Cells

    Rarely there is a requirement to add the vertical spacing in the Cells but sometimes, it may help you enhance readability. It only gives the appearance of paragraph spacing. Essentially, you are just justifying the Cell contents vertically. The following explains the process for adding vertical spacing:

 


 ****

 

I hope the above methods may increase your productivity. If you have any more ideas then please share with us.





Comments

Post a Comment

Popular posts from this blog

Adding Indian Style Commas to numbers in MS Excel - Using Cutom Number Format

Image
      Well, the better way to use Indian Style commas while working in MS Excel is to change the 'Region and Language' Setting of your Windows system. However, there is another work around in MS Excel to automatically add commas to number in Indian Style i.e. first comma at three digits and then the subsequent commas are at interval of 2 digits. For instance, 1,000,000 will appear as 10,00,000. This is particularly useful when you are working with currency in INR.   Steps to follow:     1. Select the range which needs to be formatted. Let's take the following sample data which uses the default 1000 comma separator :       2. Press Ctrl + 1. This will open the number format dialog box and then select Custom category shown below.          3.     The add the this custom number format string [>1000000]"Rs" #\,##\,##\,##0"/-";[>=100000] "Rs "##\,##\,##0"/-";"Rs "##,##0"/-" into the 'Type Text Box' as show
Read more

Do the Data Entry in your Excel WorkSheet in a right way!

Image
Introduction Let's admit that entering data in your worksheet manually is not fun. Mostly, you get your data from other database in CSV and TXT format which you then import in your sheet or you do the copy from one location and then paste in your worksheet. But sometimes, it becomes necessary to feed data manually to your worksheet before you can unleash the power of Excel. It may be noted that data entry is a slow process and, if done in haste, it becomes prone to errors especially if you have delegated the data entry work to your subordinates (in many organizations there are data entry operators for this type of work).  The Data Entry work requires constantly looking at the source material and then typing it in the designated Cell of your worksheet. In most cases, you type the contents of one cell and then press Enter or Tab to move Down or Right of your cell. In most cases, you want to move down in your Excel Worksheet in row by row manner (especially if your source material is
Read more

Take the print out of your spread sheet the way you want.

Image
  Introduction It is a common workplace issue that people face when they take the print out of their neatly created Excel sheet only to find out that one or more columns of their Excel sheet have exceeded the margin of the A4 sheet. It is frustrating and also leads to the wastage of paper.   Excel automatically divides your sheet into separate pages for printing based on margin settings, scaling option, paper size etc. without any regard to the number of columns or rows you may be having. As the number of columns of your sheet increases, it becomes more likely that your sheet will not print the way you want i.e. all columns in one page or all rows in one page. People try many ways to work around this by changing the Font Size, manual resizing of Cell width, applying Landscape orientation to accommodate more columns in one page etc. But sometimes, even after these adjustments the sheet just doesn’t fit in one page. Also, it doesn’t increase your productivity if you are doing such
Read more