Applying background to an excel worksheet

To add a picture background to an excel worksheet, Go to Page Layout ribbon >  Page Setup  > Background. (Browse to your image location and press Open)


You may have to adjust the size and resolution of your picture according to requirement


After you have applied a background, the default button changes to "Delete background" button which can be used to remove the background (if required).


You can also remove gridlines for better visibility .(removing gridlines)


Note: The background is only for on-screen display and will not be printed.

Make worksheet fit to printing page size

On the status bar, click the Page Layout button to switch from Normal to Page Layout view.

Or 


Go to Page Layout > Scale to Fit > Width > 1 page, and in the Height box, select Automatic. Excel will adjust the zoom setting to shrink the data to fit 1 page in width.

Removing Gridlines in excel

To remove gridlines in excel. Go to View > Show > Gridlines. (Uncheck the option)

Grouping Data in Excel

Grouping Data in Excel

If you have a lot of data in excel in columnar format and you want to arrange data so that only relevant information is available for view, grouping function can be used.

For example, we have data of monthly, quarterly and annual sales to different customers. Head of Sales would be interested in annual sales volume, Sales manager might be interested in quarterly or monthly sales. For that matter we can group the data for different level of detail.

Example data

To apply grouping select number of rows or columns to group and then go to Data > Group > Group

You can apply multiple grouping on same columns like a month can be grouped as a part of a quarter and at the same time it can be grouped as part of the year. After grouping all the levels available are displayed in the upper left portion of the worksheet in the form of 1,2,3......

Below is demonstration of three grouping levels applied on above data.

Level-3

Level-2

Level-1

Highlighting duplicate or unique values

Highlighting duplicate or unique values

If you have the data in excel that contains a mix of duplicate and unique values and you want to highlight either duplicate or unique values from your data.

This can be done using the "conditional formatting" option. Select the data and then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

 

By default, it highlights duplicate values in the selected range, however, the criteria can be changed to unique values from the dialog box that appears.