Automatically format cells, dates, columns and rows in Excel 2016?


Here’s a question from Krithi:

“I know that Excel has some cool capabilities for setting automatic formats (such as colors/fonts sizes / height / styles) of individual columns, rows, cells specially when it relates to dates and times. Can you provide more information about auto-formatting in Excel spreadsheets”

Great question. In this post we will show you how to add the auto format menu  in the ribbon, working with auto format and format dates and times in Excel 2016.

Adding the auto format menu to the ribbon:

  • Open your Excel 2016, right click on the ribbon and choose customize the ribbon.

2016-01-07 23_02_32-Postwork - Excel



  • Add a new tab and create a new group in the main tabs option.

2016-01-07 23_07_23-Excel Options

  • In choose commands from drop down box, select All commands.

2016-01-07 23_08_44-Settings

  • Scroll through to the Auto format entry, select it and click Add.

2016-01-07 23_09_27-Excel Options

Working with Auto format:

  • Open the Excel document which has to be auto formatted.
  • Select the cells that are needed to be formatted and click on Auto format from the toolbar or the ribbon.

2016-01-07 23_10_24-Postwork - Excel

  • Now click on your needed format and hit OK.

2016-01-07 23_12_11-Postwork - Excel

  • If needed we can customize the format by click on the options.

2016-01-07 23_13_13-AutoFormat

  • Numbers are used to add the numbers with dollar symbol or with decimal value.
  • Borders are those borders in between those cells.
  • Font helps to use text in bold letters with increased size.
  • Patters are the design patterns that can be used in the headings.
  • Width/Height increases the selected cells width and the height.

2016-01-07 23_13_59-AutoFormat

Format date cells:

Format cells is the one where each cells can be formatted separately. Like a single cell number, alignment, font, border, fill, protection. Here’s an example to format date in a cell.

  • Enter the date, right click on cell and click format cells.

2016-01-07 23_15_44-New notification

  • In number, select category as date and click on anyone of the date formats.

2016-01-07 23_16_18-Settings

Leave a Comment: