In most cases, MS Excel users are well aware of the tips and techniques of applying basic texts and numeric formats. But those who are keen to learn more are likely to create more comprehensible and effective worksheets. Knowledge of applying the accurate formatting procedure to the right cell would help users work with both time and operational efficiencies. You may also like to get more out of basic formatting features in MS Excel with these 5 excellent tips.
1. Copy Formats with Fill Handle
“Fill handle” is a potent tool that helps in copying formats along with the mere copying of formulas. It can copy formatting with these few steps:
Firstly, click on the cell whose format is going to be copied. For instance, in the first figure, click on “Monday”.
The bottom right corner of the selected cell would display “fill handle” sign. Double click on that. You will find that the cell containing “Aggregate Value” would be overwritten by “Sunday” as shown in the second figure.
Select “Autofill” option for displaying the list.
Click on “Fill Formatting Only”.
You will find that all series values have been cancelled by the fill handle. Additionally, the action would revert to the previous data. Also, you would find that the formats of the selected cell have been copied up to the targeted range. See the figure below.
You can now change the format of entire range by just changing the format of the first cell.
Compared to Format Painter, this technique is better. In Format Painter, you would otherwise have to select the entire range and then change the format; which serves to be a difficult task in case of large amounts of data.
2. Copy Styles among Worksheets
While working on a multiple set of worksheets, you may want to use the same cell style. Now, you need not spend time on re-making each style. Just follow these steps for sharing the same style among several worksheets and be surprised to see the results.
Open the main sheet consisting of the style that you want to copy and the target sheet. Both the files are to be opened simultaneously.
In the Home tab of the targeted sheet, click on Cell Styles.
Then, select Merge Styles.
A dialog box would appear. Choose the main file whose style has to be copied.
Click twice on the OK button.
By using this option you can save upon the time needed to format multiple sheets with the same style. You can save one sheet as default (main sheet) and use the same format for all other sheets. This technique is useful for presenting official reports, for example, the number of employees’ work evaluation results.
3. Format Using Shortcuts from Keyboard
Formatting is an absolute essential part in working with MS Excel as it enhances the readability of worksheets. This MS Excel feature allows you to format worksheets without compromising upon your levels of productivity. The following are some of the keyboard shortcuts that you will need every time you work with MS Excel.
CTRL + 1: It opens the “Format” dialogue box for applying to any selected cell or graph.
ALT + Enter: It assists in adding a new line to a single cell.
ALT + EST: This is helpful for pasting formats. This option showcases similar functionality like the “format painter”.
CTRL + T: On clicking this option, MS Excel would apply a function through which you can format a table. This would be applied over the present cell area.
CTRL + 5: This shortcut can be used for striking through a word or sentence.
F4: Use of this shortcut would help you accomplish the same task by repeating any previous action seamlessly.
4. Copy Formats Faster
An effective technique of copying serves to be a boon when you are experiencing a shortage of time. For doing this:
Select the cell range that you need to copy.
Right click on the border and drag it to the destination cell.
On releasing the mouse, you would see a submenu as depicted in the figure below.
Select “Copy Here as Formats Only” option.
5. Formulate Cell Style indicating Purpose
You may like to use the Cell Style option for signifying a purpose and assisting your users for getting accustomed to that purpose. For instance, you might utilize different colors for distinguishing between label and input cells. The Cell Style option would help you apply that principle into work without any fuss. Let’s see how:
After opening MS Excel, go to “Home”. Then select Cell Styles.
Click on New Cell Style
A dialog box would appear. Name a style beside the option “Style Name”. For example, Beautiful.
Click on Format.
Select Border and choose “Outline” below.
Select Fill and click on a color, say light purple.
Click on the OK button to display the selected formats as shown in the figure below.
Then, click on OK again.
After these steps, anytime, if you want to point out a range, then follow the following steps:
Choose the cell or range.
While on “Home”, click on Cell Styles.
Click on Beautiful as shown in the figure and MS Excel will do the rest. You will find that the same style has been applied to the selected cell.
So, gain excellence in MS Excel with these above mentioned simple formatting tips. They are certainly going to improve your work experience.