Cell formatting in Excel allows you to represent your numbers in a variety of ways: as a date, as a percentage, as currency, and with custom formatting, even phone numbers. In today’s quick tip, we are going to show you how to quickly change a number to a percentage in Excel.

After entering a number or an array of numbers into different cells, you can format them (or format the cell before the number is entered – your preference).

To convert the values to a percent:

• Highlight the desired cells
• Right click them
• Click the Format Cells option
• Click the Number category
• Then chose the percentage tab

This will give you the option to change how many decimals points you desire to show. After you have picked your desired decimal points, click OK and bam, it is now a percentage!

Tip: you can also click on the lettered column heading to convert the all cells within the column to your formatting preference. This is good for spreadsheets where you will be adding values over time, so that you don’t have to keep reformatting each time.

Note:  if you apply the percentage format to existing numbers in a spreadsheet, excel multiplies the numbers by 100 to convert them to percentages. (If a cell contains 20, it will covert to 2000.00 %).

To format empty cells, follow the same procedure for formatting cells with existing values. Tip: you can also click on the lettered column heading to convert the all cells within the column to your formatting preference. This is good for spreadsheets where you will be adding values over time, so that you don’t have to keep reformatting each time.

As with many functions in Excel, there is another way to this as well using the Ribbon – the top toolbar in Excel.

To change numbers to a percentage in Excel using the Ribbon, click on the Ribbon, make sure you are on the home Ribbon tab. The go to the number tab and use the dropbox to select percentage. On this tab, you can also use the decimal arrow keys to select the number of decimal places you want.

We hope you found today’s quick and easy lesson on converting numbers to a percentage in Excel beneficial. Don’t forget to follow ups on Social Media and subscribe to the blog to get convenient, quick tips like this, and other great Excel training tips so that you can take the fear out of Excel.

Like Learn Excel Now? Sign up for the newsletter!

-Kevin, Learn Excel Now

When it come to Excel formatting, there are so many options and tools that it can be a little overwhelming trying to determine what to use and how to use it. Luckily, there are some beginning steps that can help you focus on where to begin.

Special Paste

In the following article, we will start with some of the more basic tools and features you can use and then get into an Excel formatting macro as a suggestion on something you can do once you’re comfortable with the basic stuff.

Freezing Column Headings

When you’re working with a long excel spreadsheet, you might want to see what your column headings are so that you don’t have to keep scrolling up or make a mistake.

To make this easy, freeze the column headings. This will always keep your first row with the headings at the top of the columns regardless of how far down you scroll.

To set this, click on “View” on the tool bar, and then select “Freeze Panes.” Click on the “Freeze Top Row” option.

Quick Tips For Adjusting Columns and Rows: Your Handy Excel Formatting Cheat Sheet

• To quickly insert a column or row, select the row or column by clicking on a corresponding cell, then click “Ctrl” and “+”
• To quickly delete a column or row, select the row or column by clicking on a corresponding cell, then click “Ctrl” and “-”
• Autofit makes the columns big enough to fit their contents. To do this, select the column(s) that you want to change, then click on the “Format” icon on the tool bar and select “AutoFit Column Width.”
• Another way to adjust the column size is to fit it to the text you enter. To do this, first enter your text, then double click the separating line between that column and the column to the right of it. The column will enlarge to fit the text.

Key Board Shortcuts to Help you Save Time in Excel Formatting

• “Ctrl” + “b” à Bold
• “Ctrl” + “i” à Italic
• “Ctrl” +” u” à Underline
• “Alt” + “=” à Auto Sum

Formula Tricks for Quick Excel Formatting 

To copy a formula down the length of a column, click on the cell with the formula. Then, put your cursor in the bottom right corner of the cell until you get the thick, black plus sign, you can either drag the formula down or double click and the formula will be copied down the length of the column.

However, keep in mind that this will adjust the formula in each row to correspond to the data, in the same row as the formula. So, if you have a value that will stay the same in each formula you must add “$” before the letter and in between the letter and the number of the cell you want to keep the same (for example, $A$1)

Excel Formatting Macros

Sometimes, using a Macro can help you save time in Excel formatting. To create formatting macros, first select the cells that you want to format. Then go to the “View” tab on the tool bar. Click on “Macros,” then select “Record Macros.” You will be prompted to name and provide a description of the macro.This could be useful in a spreadsheet where you are using multiple macros. Record the keystrokes to format the cells that you have selected. When you’re finished go back to “Macros” and select “Stop Recording.”

To use the macros you recorded, go to “Macros” and select “View Macros.” You will get a list of all the macros you recorded. Select the one you want to run and then click “Run.”

We here at Learn Excel Now hope you feel more comfortable using formatting.

Getting social with Excel: please feel free to share this article with anyone you think would benefit from it.

Kevin – Learn Excel Now

One of the most common fears of Excel is getting unruly data files and not knowing how to quickly and easily format it to look nice. Here we will go over when and how to auto expand multiple columns in Excel.
[Read more…]