So formatieren Sie Zellen in Excel

If you are wondering how to format cells in excel, you have came to the right place. Small formatting adjustments can make all the difference to a Microsoft Excel workbook. A small pop of color here, a change of font there, and suddenly, your workbook is no longer just a sea of rows and columns, but an organized, presentable table of data. Knowing how to format cells in excel will help you in your data organization and preparation!

Below, we take a closer look at how to format cells in Excel tables using the six tabs found in the Format Cells dialog box, followed by some Excel format shortcuts. 

What Does it Mean to Format Cells in Excel?

Microsoft Excel offers several controls that allow users to change how their data is displayed. And there’s good reason to do so—formatting cells in excel can draw attention to important data or more accurately display the contents at hand (for example, adding $ to cells that contain values pertaining to prices or configuring cells that represent dates to a standard display of xx/xx/xxxx). 

Formatting cells in excel is an optional step following data preparation, or all of the data cleansing, enriching, structuring, and standardizing that is required in order to prepare data for analysis. 

New data rarely arrives without its own unique set of challenges; it’s up to analysts to comb through their data and ensure that it is prepared to fit the specific needs of their analytic project. This could include splitting columns, removing rows with missing data, or standardizing against a certain name (e.g. “CA” or “Calif.” becomes “California”). 

Once completed, Excel formatting adds the finishing touches so that data is both accurately prepared and presented. 

What Are the Six Tabs in the Format Cells Dialog Box?

The six tabs in the Format Cells dialog box include: Number, Alignment, Font, Border, Patterns, and Protection. 

Number Tab

The Number tab allows you to format the way that your numbers will be displayed. You can choose from options such as percentages, dates, currency, times, etc. 

1. Start by selecting the cells you want to modify. 
2. On the Home tab, select Format > Format Cells, which will open the Format Cells dialog box. 
3. The first tab listed is the Number tab. The Category list in the Number tab allows you to select the format you want to use, such as Date, Time, Percentage, Currency, etc. You will also have the option to further customize your selection. For example, you could select a specific way that you’d like to represent negative currency values (see image below). 

Source: Microsoft Support 

Alignment Tab

Automatically, Excel formats any text to the bottom-left of a cell and numbers to the to the bottom-right. The Alignment tab within the Format Cells dialog box allows you to customize the way that you’d like your values to be aligned, both Vertically or Horizontally. 

Should you require a more dramatic text alignment, the Degrees field allows text to be oriented 90 degrees in either direction up or down.

Text Control allows you to control the way Excel formats information in a cell. There are three types of text control: wrapped text, shrink to fit, and merge cells. 

And finally, Text Direction switches the direction of the worksheet—in other words, column A could start from the upper right side instead of the upper left. 

1. To make any of these modifications, first select the text that you’d like to modify. 
2. On the Home tab, select Format > Format Cells, which will open the Format Cells dialog box. 
3. Click on the Alignment tab. From there, you will see Text Alignment (Horizontal; Vertical), Text Control (Wrap text, Shrink to fit, Merge cells) and Text Direction (Context; Left-to-Right; Right-to-Left).

Font

Quick Font changes can be made directly from the Home tab, but for mass changes, it’s more efficient to use the Format Cells dialog box. From there, it’s easy to change typeface, point size, font size, bold, italicize, underlining, and color across an entire selection of cells. 

1. To make any of these modifications, first select the text that you’d like to modify. 
2. On the Home tab, select Format > Format Cells, which will open the Format Cells dialog box.
3. Clicking on the Font tab will prompt the possible selections, as well as a preview of your changes. 

Border

Excel allows you to construct borders around a single cell or a range of cells. You can determine where the lines will be drawn (for example, only the top of the cell or on all horizontal sides) and adjust their thickness, color, and line style. 

1. To make any of these modifications, first select the text that you’d like to modify. 
2. On the Home tab, select Format > Format Cells, which will open the Format Cells dialog box.
3. Clicking on the Border tab will prompt the possible selections. If you want to remove a specific border, click the button for that border a second time.If you want to change the line color or style, click the style or color that you want, and then click the button for the border again.
   

Fill

The Fill tab in the Format Cells dialog box allows users to set the background color of all selected cells, which may include applying two-color patterns or shading from the Patterns option. Here’s how to shade cells with Patterns: 

1. Start by selecting the text that you’d like to modify. 
2. On the Home tab, select Format > Format Cells, which will open the Format Cells dialog box.
3. Click on the Fill tab, then select the Pattern Style you’d like to set as the background to your cells. As an optional addition, you may also choose to set a Pattern Color to accompany your Pattern Style. 
4. At any time, you can return to the default state of your selected cells by choosing No Color at the top of color selection. 

Protection

The Protection tab does not apply unless you have already protected your worksheet. To do so, click on Protection in the Tools menu, select Protect Sheet, and then select the Contents check box(es) to determine how, exactly, the worksheet will be protected. 

When the Locked option is selected, you are prohibited from doing the following: 

• Changing the cell data or formulas.
• Typing data in an empty cell.
• Moving the cell.
• Resizing the cell.
• Deleting the cell or its contents.

When the Hidden option is selected, that means that all formulas used to calculate values will no longer be viewable in the formula bar (however, you can still see the end result of that formula). 

Recycling Excel File Formats

After you’ve formatted your Excel worksheet exactly how you want it using the six primary Excel file formats, odds are, you don’t want to keep repeating that work. Here are some of the ways that users can recycle their Excel formatting process. 

Copying Styles Between Workbooks

With each new worksheet, there’s a way to copy over previous Excel file formats from an original worksheet. 

1. First, open the workbook with your original Excel formatting and the new workbook.
2. From the original workbook, click Cell Styles in the Styles group on the Home tab. 
3. Choose Merge Styles at the bottom of the gallery.
4. In the resulting dialog, select the open workbook that contains the styles you want to copy. 
5. Click OK twice.

Copy Over Formats

Sometimes you just want to copy over formatting from one column to another (without the values). In that scenario, it’s easy to copy over formatting only.

1. First, start by selecting the destination cell or range of cells. 
2. Then, right-click the border of the cell with your preferred formatting and drag it to the target cell or range of cells. 
3. When you release the mouse, Excel will display a submenu where you can select “Copy Here,” “Copy Here as Values Only” or “Copy Here as Formats Only.” In this case, you’d want to select the third option, leaving the cells blank but prepped with the correct formatting.

Use Paste to Copy Formatting

In a similar vein, you can use the Paste function to copy over formatting from one column to another. 

1. You’ll start by selecting and copying [Ctrl]+C your original cell or range of cells.
2. Then, click anywhere instead your destination cell or range of cells. Press [Ctrl]+[Spacebar] to select the entire column or [Shift]+[Spacebar] to select the entire row. 
3. Once you’re ready to paste over the formatting, choose Formatting from the Paste drop-down. 
4. Live Preview will show you what the applied formats will look like, and you can click OK if everything looks good. 

What’s Next

Knowing how to format cells in excel is an essential task to make sure that data is consistent and presentable. But data formatting would be nothing without proper data preparation; analysts need to ensure that their data is accurate and clean data before they can format it for review. 

The Designer Cloud data preparation platform offers a powerful alternative to preparing data in Excel. Instead of manual transformations, the Designer Cloud platform is guided by machine learning; instead of mere rows and columns, the Designer Cloud platform visually presents statistics about the data so that analysts can understand the bigger picture of their data. All of that and more has allowed Designer Cloud customers to see up to a 90% reduction in time spent preparing data. 

To learn more about data preparation and the Designer Cloud platform, try the product out for yourself for free.