All about formatting cells in Excel

24
20724
Excel Spreadsheets

No Excel, each cell is independent of the others, displaying several types of data. With so much information, it is important that these cells are formatted. Thus, your data becomes more organized and clear to anyone who uses your spreadsheet.

Excel Online Course

To format cells, use the formatting toolbar or right-click on the cell you want to format and choose "Format Cells" as shown below:

format cells

The "Format Cell" dialog box is divided into 6 parts: Number, Alignment, Font, Border, Fill, and Protection.

The Number option is subdivided into several categories, as explained below:

General - This option defaults to each cell. Texts and numbers will appear the way they are typed. Example: 23,07 and 23.07, both will be accepted;

Número - Here you have the possibility to format the cell as number and choose the desired decimal places;

Currency - Change the currency cell with the desired dollar sign. The default is Real, but you can choose the Dollar (USD), for example.

Accounting - Unlike the previous item, in spite of transforming the cell into the desired currency, in this type of formation zero values ​​are shown as dashes, aligning all currency symbols and decimal places, and negative values ​​display in parentheses, making it easier for company accounting .

Date and time - It is used to identify the date or time of day when the worksheet is being performed. The most commonly used options are dd / mm / yyyy or mm / dd / yyyy.

Percentage - Changes the cell to the percent format;

Fraction - Format the cell (number) as a fraction;

Scientific - Here it is possible to use the scientific form (E of exponential) in the desired cell;

Text - Check this selection when entering only text and specific numbers;

Special - In this option you can format in any type of number you want and even create new formatting.

All about formatting cells in Excel 1

The Alignment option has 4 types of formatting to be used in the text, they are:

Text alignment - Selects the type of text alignment: left, right, centered or justified;

Text Orientation - Choose how the text will appear: vertically or horizontally or even at some intermediate angle between the two;

Control of text - Break the text automatically, reducing it to fit in the cell or merge the selected cells;

Direction of the text - Here it is possible to change the direction of the text, that is, whether it will start from left to right or from right to left or in context format.

format cells alignment

The Font option has 5 possible formatting for the cell:

Source - In this option, you select the desired source;

Underline - Lets you choose the type of underline or no type of underline;

Effects - Changes cell for strikethrough, superscript and subscript;

Font style - Format the contents of the cell from the regular, italic, bold or bold italic options;

Size - Used to change the size of the desired font;

Color - Changes the text color or number present in the cell.

formatting source cells

Excel Online Course

The Border option has 4 options to choose from:

Line - Causes the cell to have a border with a certain type of row from the available options;

Color - Lets you choose border color;

Definitions - Here you predefine the desired border type in the cells between: none, full contour or internal contour;

Edge - Formats cells with other border options.

format cells border

The Fill option has 3 possible formatting:

Background Color - Fill the cell with the chosen color;

Pattern Color - The background color of the cell is change to the chosen texture or color;

Pattern Style - You can choose the desired pattern from the available options;

format cells fill

The last option, Protection, allows you to hide or block a cell, if desired;

protect cells

Excel Online Course

As you can see, there are several possibilities for formatting cells. Just choose the one that applies to your data. Good job!

Excel Spreadsheets

24 COMMENTS

  1. Hi Victor, this may be a problem with the spreadsheet CALCULATION being as MANUAL (on the FORMULAS tab). If not, I don't know what can be

  2. Good morning,

    I formatted a column of phones as (##) #### - ####.
    Some of them were formatted, some not.
    If I click as if I would edit the cell contents and hit enter the cell is formatted as it should be.
    How to solve this without having to open each unformatted cell?

    Thank you in advance.

  3. Hi Doris, you can use the filter (DATA tab> FILTER) to sort the items in order from smallest to largest or otherwise

  4. how do I correct a number in the worksheet, I'm trying, but when the line changes the numbers do not appear in the order on the next line.

  5. Hi Cledison, yes you can, but you will have to do this using conditional formatting. I usually use the following logic in some of our spreadsheets - in one cell I write percentage or currency, and in another, conditional formatting applies according to what was written in that first

  6. Hi Rafael, how are you?
    I have a question: is it possible to put 2 data types in the same cell? For example, put accounting and percentage and it automatically change according to the type of data that I put?

  7. Hi Carolline, the only way I know is via conditional formatting even in the table fields used. If you are changing there correctly, in theory, if you leave the formatting of the graphics as general, it should adapt to the formatting of the table used

  8. Hello Rafael, how are you?
    I created a chart with a combo box to show results. It has a combo box that looks for the indicators in another worksheet and displays the results (Ex: Total Sales in Numbers, Total Sales in Reais,% Total Bonus). Some indicators are arranged in Accounting format and other indicators are arranged in Percentage. When I select an indicator that is in percent, the formatting of your data does not automatically change, that is, the graph axis labels do not display the percent values. I tried to perform conditional formatting of the spreadsheet data that is generating the chart, but it was not effective. I would like to know a way to perform a conditional formatting of the chart labels or the sheet number format so that all data can be displayed in a single chart.

  9. Hi Moses, you can use the LOCATE AND SIBSTITUTE functionality to swap points with commas

  10. Good afternoon, I downloaded a spreadsheet in excel format that came with cells with the number XXXX.00 number followed by period, as if it were a comma. I've tried several ways to clear formatting, change Xnumx nr to comma, format as number but I could not. The problem is that historical data is downloaded from ANA's website, that is, huge numbers of numbers to change manually. You can help me?

  11. Just select all the columns you want to edit (if it is on more than one tab, just select the tabs before) and change the width of all at once

  12. Hi Rafael,
    Can you resize the entire worksheet in terms of column width except the horizontal menu? That is, keep the menu static for resizing.

  13. Hi Italo, formatting does not compute, but yes, you can create formulas to create multiplications or automatic sums

  14. Hi Rafael, in SPECIAL formatting it is possible to use a formula to calculate.
    for example, for any number that I type it automatically multiply by one or some, finally a spice of direct automatic calculation in the formula.

  15. Hi Carlos, in these cases a good solution may be to paste the values ​​or formulas. So it maintains the original destination formatting

  16. It works for typing, but does not work if you use "copy - paste" from another worksheet or file, in which case the text formatting is maintained.
    Example: My worksheet is configured with Calibri, 11, Regular. I copied a line from another worksheet that was in Times, 16, Bold, and pasted to my worksheet. It stayed Times, 16, Bold and how protected, does not accept nor format and return to Calibri, 11, Regular.

  17. Yes you have, just select the protection formatting (cell formatting same) of these cells as NOT PROTECTED (just uncheck the check that has the). Then you go to the REVIEW tab and protect the spreadsheet in question. You will see a window that allows the user to fill the unlocked cells, but does not allow formatting, etc.

  18. Rafael, good afternoon. Excellent your blog, I visit frequently to get my doubts about Excel.
    Even today, I have one doubt that is as follows:
    I want to lock or lock only the formatting of some cells, but I want to allow the addition of data. It is possible?
    Example: I have a spreadsheet commonly used by other colleagues at work. In it I formatted Column B, as follows: Date dd / mm / yyyy; Calibri Font - Regular - Size 11; Alignment Center; and Edge with Traced Line.
    I want to allow others to include data in this worksheet, but I do not want these people to change this setting (keep format, font, size, border, etc.). So I can not block as explained in the text above.
    Is there any way to do this?
    Hug
    Carlos Henrique

  19. Hi Jean, for this you need to select the cell or range that you want to apply formatting, go to the INITIAL PAGE tab, NUMBER group and in the list choose MORE NUMBER FORMATS. In the window that will open you can go in the CUSTOM (last) option and in the TYPE text box use the format you want

  20. Hi. You are advised that you can create special formats in Microsoft Excel. Could you please explain to me how?

LEAVE AN ANSWER

Please, write your comment
Please enter your name