Home > Format > Tips > Worksheet

Excel Formatting Tips

Debra Dalgleish - Contextures

Stop number signs in narrow columns in Microsoft Excel. Show or hide floating mini toolbars. Macro to hide right-click mini toolbar. Create a color spectrum across a range of cells.

fill effects gradient for color spectrum

1) Open the Format Cells Dialog Box

Open the Format Cells with the keyboard or mouse, to change any of the formatting options for the selected cells

Open With a Keyboard Shortcut

  1. Select the cells that you want to format
  2. On the keyboard, press Ctrl + 1

Open With the Mouse

  • Select the cells that you want to format
  • Right-click on one of the selected cells
  • Click Format Cells

The Format Cells dialog box opens, and you can click any of the tabs, to change the formatting options for the selected cells.

Format Cells dialog box

2) Copy Formatting Only

Do you use this mouse shortcut to quickly copy formatting? I was helping someone with conditional formatting, and we set up the rules in the first cell.

Then, to copy those rules to the cells below,

  • Point to the Fill Handle, and press the RIGHT mouse button
  • Drag down to the end of the list
  • Release the mouse button, and click the command -- Fill Formatting Only.

The animated screen shot below shows the steps -- I find this quicker than using the Format Painter.

If you prefer to use the keyboard, there is a long list of Excel Keyboard Shortcuts on my website.

Fill Formatting Only

3) Narrow Column Number Signs

In the screen shot below, all the cells are the same font and font size - Calibri 11.

  • In cell D3, number signs are showing, because the column is too narrow to show the full number.
  • In cell D8, the same number shows correctly, because of a special cell format setting.

Smaller font in some headings

In this short video, I show how to fix the problem, when number signs appear in narrow columns. There are written steps below the video.

3.1) Fix Number Signs in Narrow Columns

To prevent number signs from replacing the real numbers, you can turn on a setting for the cell's font.

The setting is named"Shrink to Fit". With this setting turned on, if you make columns narrower, the font shrinks, if necessary, so all the text fits in the current column width. This setting will save you time, because you won't have to constantly tweak the font sizes or column widths.

To change the Shrink to Fit setting, follow these steps:

  • First, select the cells, where you want to apply the font setting change
  • Next, press Ctrl+1, to open the Format Cells window
  • Click the Alignment tab, and add a check mark to Shrink to Fit
    • NOTE: This is not available if Wrap Text is turned on
  • Click OK to close the window

After this setting change, the font will get smaller, if the column is too narrow to show all the text. However, the font will NOT get bigger than the set font size, if you make the columns wider.

4) Custom Colour Trick

If you're trying to select a custom colour, the colour palette that opens is very small, and it's hard to click on the colour you need.

Use this quick trick to make the job easier.

  • When the palette opens, double-click the title bar, at the top of the palette window.
  • The colour palette will automatically expand in size, to fill the entire screen.

In the screen shot below, I'm pointing to the title bar, and ready to double-click there.

color palette

5) Mini Formatting Toolbars

While working on a worksheet, you might want to format a cell, or the text inside a cell.

To do that, you can use the formatting commands on the Excel Ribbon's Home tab. Or, to same time, use one of Excel floating mini toolbars.

5.1) Text Formatting Mini Toolbar

There's a floating toolbar that appears when you select text in a cell. It has a few commands for formatting text.

floating toolbar

Show or Hide Text Format Mini Toolbar

To turn this mini toolbar on or off, follow the steps below:

  • At the top left of Excel, click the File menu
  • At the bottom left, click on Options
  • At the left, click the General category
  • In the User Interface Options section, look for the setting, "Show Mini Toolbar on selection".
    • To turn ON the toolbar, add a check mark for that setting
    • To turn OFF the toolbar, remove the check mark for that setting

mini toolbar option setting

5.2) Cell Formatting Mini Toolbar

There's another mini toobar that appears when you right-click on a cell. This toolbar is larger, and has commands for text formatting, and cell formatting.

show or hide right-click mini toolbar

Show or Hide Cell Format Mini Toolbar

Unfortunately, there isn't a setting in Excel Options, where you can turn the right-click Cell Format mini toolbar on or off.

Instead, you can use the following macro to show or hide this toolbar.

Sub MenuFloatiesToggle()
'contextures.com
  'show/hide right-click
    'mini toolbar
  With Application
    .ShowMenuFloaties _
      = .ShowMenuFloaties
  End With
End Sub
How to Save and Use the Macro

To save this macro, I pasted the code into my Personal Macro Workbook, which opens automatically when Excel starts.By storing it there, I can use the macro from any workbook, while Excel is running.

  • Most of the time, I like the toolbar showing. But, if I'm recording an Excel tutorial, I usually turn it off. Otherwise, it can cover up part of the screen that I'm trying to show!

To run the macro:

  • On the Excel Ribbon, go to the View tab
  • At the far right, click the top part of the Macros command
  • In the list of macros, click on Personal.xlsb!MenuFloatiesToggle, to select it
  • At the right, click the Run button.

Assign Macro Shortcut

To make the macro easier to run, you can assign a shortcut key.

  • On the Excel Ribbon, go to the View tab
  • At the far right, click the top part of the Macros command
  • In the list of macros, click on Personal.xlsb!MenuFloatiesToggle, to select it
  • At the right, click the Options button.
  • In the Macro Options dialog box, click in the Shortcut key box
  • Type the letter you want to use, or press Shift, then type a letter.
  • In the screen shot below, I pressed Shift, and typed the letter F (for Floaties)
  • Click OK, to save the shortcut, and to close the Macro Options dialog box.
  • Click Cancel to close the Macro dialog box.

color palette

6) Show the Color Spectrum

To show an electromagnetic spectrum in a range of 6 cells, you can use a 2-color gradient fill in each cell.

Thanks to Robert Lepper, for sharing this tip. He used this technique to help with a homework assignment.

fill effects gradient for color spectrum

To show a color spectrum in an Excel spreadsheet, like the one above, each cell is filled with a 2-color gradient. Each adjacent cell starts with the finishing color of the cell to the left.

To fill each cell:

  1. Right-click on a cell, and click Format Cells
  2. In the Format Cells dialog box, click the Fill tab
  3. Below the palette of background color options, click the Fill Effects button
  4. fill effects button

  5. Under Colors, the Two Colors option is automatically selected.
  6. Select your starting and ending cell colors in the Color 1 and Color 2 drop downs
  7. Under Shading Styles, select Vertical
  8. fill effects dialog box

  9. Click OK, twice, to close the dialog boxes
  10. Repeat these steps in each of the 6 cells, to create the full color spectrum.

fill effects gradient for color spectrum

Get the Sample File

Get the sample formatting workbook. The zipped file is in xlsx format, and does not contain macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

FAQs, Worksheet Functions and Formats

Data Entry - Tips

Conditional Formatting - Basics

Drop Down List in Cell - Basics

 

 

 

Last updated: November 10, 2025 3:58 PM