How do you fill cells with dots?

In this article, you will learn 5 Excel tricks on how to format cells in Excel. Applying these in excel will make your worksheet or spreadsheets look attractive and easy to read. Almost all Microsoft Excel users know how to change the date format, numbers, and text formats using rows and columns.

Table of Contents Show

Let’s discuss 5 Tricks to Format Cells Data in Excel
1. Date Format
2. Trailing Dots
3. Currency Format (Prefix)
4. Format Cells in Excel (Prefix)
5. Format cells in Excel (Suffix)
How do I fill a cell in Excel with dots?
Can you fill an Excel cell with a pattern?
How do you put dots between words in Excel?

But very few know how efficiently and quickly customize it to suit your requirements. These excel tricks & tips are covered in detail in our video-based advanced Excel Training on our online school for professionals.

Excel Vlookup formula – Guidebook

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

Let’s discuss 5 Tricks to Format Cells Data in Excel

1. Date Format

For example, you have a long set of dates in cells like below:

And you want to change the date format as a dd-mm-yyyy format. So that it can show the date format like 2-Apr-2014, which is easy to read. Normally what we do is, we select the entire range of cells. Then we right-click and then click on Format cell in excel. But there is a shortcut key for this. Simply press ctrl +1 to format cells in excel.

So after selecting the range just press ctrl+ 1.
Then select date category and then choose your Date format from the selection pane at the right

Now you have your date as per your required date format. Besides this, there is also another trick if you want to show your date as dd-mmm-yyyy format. Select your range and then press Ctrl+ Shift + 3.

Now your date is properly formatted with one shortcut key. Remember, the shortcut key is Ctrl + Shift + 3 to format a date as a dd-mmm-yyyy format.

2. Trailing Dots

Here I will talk about how to format cells in excel with trailing dots. For example, you have a list of names of your clients. I want to add dots (.) so that it looks good and is also self-adjusting. Let’s look at this example.

I have the list of client names and I want to add dots (.) so that it looks good. You may have questions running in your mind regarding this technique of formatting cells in excel – How will we achieve this? – Will we go ahead and manually put dots (.)? – What will happen if I delete a few letters from a client’s name? – Will it be self-adjusted?

Solution:

Select your range where you want to put dots (.). You can select the entire column and then press Ctrl +1 and dialog box format cells in excel will open.
Then go to custom and in place of general type @*.
As soon as you will press ok you will see the trailing dots are added and it is self-adjusted. If you want to test then delete a few letters from a client’s name and then check. You can also change the width of the column. The dots will be self-adjusted.

3. Currency Format (Prefix)

I have a large recordset of the amount. I want to add prefix i.e. Rs. in my records so that I can apply sum, average, min, max function. I can easily do it by typing it manually but then I will not be able to apply any function. If I enter any amount in this column I will need to add Rs. prefix manually. If you know this trick then you do not have to do this manually.

Now from the above picture, it is clear what my raw data is and what I actually want.

Select your range and then press Ctrl +1.
Now I am going to custom format in excel and then type “Rs.” 0.0. This ensures that numbers are there with one decimal.

15 Excel Data Cleaning Tricks Guidebook

Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages)

As soon as you click on ok you will find that numbers are formatted with Rs. as a prefix. And it will look like below.

Notice these are numbers with one decimal but I don’t find any comma-separated-values, the way you have in million and lakhs. So what will be my approach? To apply this you need to go to the home tab then apply the comma separator, reduce one decimal and then press ctrl +1 and press custom.

You will find a long list of custom codes that signifies comma separator should be with the million and lakhs. Now before the starting of the custom code put “Rs. “. You will notice in the preview section Rs. has been added. And then click on OK.

Excel Vlookup formula – Guidebook

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

As you press ok you get the benefit of both prefix Rs. and the numbers with a comma separator.

4. Format Cells in Excel (Prefix)

Now I will tell you how to add prefix “00” for a check number. For example, you have data like below (before).

Now the point is how you will format cells in excel so that total digit should look like a six-digit code. It is possible to add zeroes manually. But the zeroes will go away until and unless you change the format of the cells as text. And if you do so you will not be able to apply any functions on these cells.

So the trick is to choose all the cells and press ctrl +1 to get the format cell dialog box.
Now go to custom in general type six (6) zeroes.

Notice as soon as you type six zeroes, in the preview, you can see it has added two zeroes before the numbers. And then press ok. Now you are done. This is the format you are looking for. Remember one thing is just the format and the length of the character does not change. It is just for representation purposes.

5. Format cells in Excel (Suffix)

Suppose you have a weight record of children and it looks like below:

You want to change the column as the ‘After section’. You can add another column and write “Kg“, and then concatenate these fields. But again the same problem will be there and you not be able to calculate. So how will you format cells in excel such that it looks like “kg” is there and you are also able to get the sum of the total age? Here is what you need to do.

First, select the cells you want to get formatted. Or you can choose the column of the entire cell and then press ctrl +1 to get the format cell dialog box.
From the category, tab chooses a number and then click on custom.
In the type box please type 0″ Kg”.

“Kg” suffix is now added to your chosen cells. Now if you want a sum of these cells, you write the formula and the result will be also formatted as “Kg” (if you format the entire column).

I will talk about the more techniques in my later blogs. Just practice all these and check how friendly this is. Please let us know your feedback.

If you liked these techniques for format cells in excel, do share it with your friend and colleagues

15 Pivot Tables Tricks for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.

How do I fill a cell in Excel with dots?

How to add bullet points in Excel using Symbol menu.

Select a cell where you want to add a bullet point..

On the Insert tab, in the Symbols group, click Symbol..

Optionally, select the font of your choosing in the Font box. ... .

Select the symbol you'd like to use for your bulleted list and click Insert..

Can you fill an Excel cell with a pattern?

Apply a pattern or fill effects Click Home > Format Cells dialog launcher, or press Ctrl+Shift+F. On the Fill tab, under Background Color, pick the color you want. To use a pattern with two colors, pick a color in the Pattern Color box, and then pick a pattern in the Pattern Style box.

How do you put dots between words in Excel?

With the REPLACE function, we can add any arbitrary character to a string. For example, REPLACE(“1234567890”;5;0;".") returns the string '1234.567890' where a dot is placed as the fifth character. We can use multiple REPLACE functions in a single formula to keep adding characters in the necessary places.