1. AutoFormat
If you deal with financial data, then auto format can be one of your best tools. It simply applies the format to small as well as large data sets (especially when data is in tabular form).
- First of all, you need to add it to the quick access toolbar (here are the steps).
- After that, whenever you need to apply the format, just select the data where you want to apply it and click on the AUTO FORMAT button from the quick access toolbar.
- It will show you a window to select the formatting type and after selecting that click OK.
The AUTOFORMAT is a combination of six different formattings and you have the option to disable any of them while applying it.
2. Format Painter
The simple idea with the format painter is to copy and paste formatting from one section to another. Let’s say you have specific formatting (Font Style and Color, Background Color to a Cell, Bold, Border, etc.) in the range B2:D7, and with format painter, you can copy that formatting to range B9: D14 with a click.
- First of all, select the range B2:D7.
- After that, go to the Home Tab ➜ Clipboard and then click on “Format Painter”.
- Now, select cell C1 and it will automatically apply the formatting on B9: D14.
The format painter is fast and makes it easy to apply to format from one section to another.
Related: Format Painter Shortcut
3. Cell Message
Let’s say you need to add a specific message to a cell, like “Don’t delete the value”, “enter your name” or something like that.
In this case, you can add a cell message for that particular cell. When the user will select that cell, it will show the message you have specified. Here are the steps to do this:
- First, select the cell to which you want to add a message.
- After that, go to the Data Tab ➜ Data Tools ➜ Data Validation ➜ Data Validation.
- In the data validation window, go to the Input Message tab.
- Enter the title, and message, and make sure to tick mark “Show input message when the cell is selected”.
- In the end, click OK.
Once the message is shown you can drag and drop it to change its position.
4. Strikethrough
Unlike Word, in Excel, there is no option on the ribbon to apply strikethrough. But I have figured out that there are 5 ways to do it and the easiest of all of them is a keyboard shortcut.
All you need to do is select the cell where you want to apply the strikethrough and use the below keyboard shortcut.
Ctrl + 5
And if you are using MAC then:
⌘ + ⇧ + X
Quick Note: You can use the same shortcut keys if you need to do this for partial text.
5. Add Barcode
It is one of those secret tips that most Excel users are unaware of. To create a bar-code in Excel all you need to do is install this bar-code font from ID-AUTOMATIC.
Once you install this font, you will have to type the number in a cell for which you want to create a bar code and then apply the font style.
6. Month Name
Alright, let’s say you have a date in a cell, and you want that date to show as a month or a year. For this, you can apply custom formatting.
- First, select the cell with a date and open formatting options (use Ctrl + 1).
- Select the “Custom” option and add “MMM” or “MMMMMM” for the month or “YYYY” for the year format.
- In the end, click OK.
Custom formatting just changes the formatting of the cell from date to year/month, but the value remains the same.
7. Highlight Blank Cells
When you work with large data sheets it’s hard to identify the blank cells. So, the best way is to highlight them by applying a cell color.
- First, select all the data from the worksheet using the shortcut key Ctrl + A.
- After that, go to Home Tab ➜ Editing ➜ Find & Select ➜ Go to Special.
- From Go to Special dialog box, select Blank and click OK.
- At this point, you have all the blank cells selected and now apply a cell color using font settings.
…but you can also use conditional formatting for this
8. Font Color with Custom Formatting
In Excel, we can apply custom formatting and in custom formatting, there is an option to use font colors (limited but useful).
For example, if you want to use the Green color for positive numbers and the red color for negative numbers then you need to use the custom format.
[Green]#,###;[Red]-#,###;0;
- First, select the cells where you want to apply this format.
- After that open the format option using the keyboard shortcut Ctrl + 1 and go to the “Custom” category and the custom format in the input dialogue box.
- In the end, click OK.
9. Theme Color
We all have some favorite fonts and colors which we use in Excel. Let’s say you received a file from your colleague and now you want to change the font and colors for the worksheet from that file. The point is, you need to do this one by one for each worksheet and that takes time.
But if you create a custom theme with your favorite colors and fonts then you can change the style of the worksheet with a single click. For this, all you have to do is apply your favorite designs to the tables, colors to the shapes and charts, and font style, and then save it as a custom theme.
- Go to the Page Layout Tab ➜ Themes ➜ Save Current Theme. It opens a “Save As” dialogue box, names your theme, and saves it.
- And now, every time you need just one click to change any worksheet style to your custom style.
10. Clear Formatting
This is a simple keyboard shortcut that you can use to clear formatting from a cell or range of cells.
Alt ➜ H ➜ E ➜ F
Or, otherwise, you can also use the clear formatting option from the Home Tab (Home Tab ➜ Editing ➜ Clear ➜ Formats).
