Skip to main content

Formatting In Excel - helps you find meaning in the spreadsheet


 


Formatting In Excel -  helps you find meaning in the spreadsheet 


Spreadsheets are often seen as boring and pure tools of utility, but that doesn't mean that we can't bring some style and formatting to our spreadsheets

Formatting helps your user find meaning in the spreadsheet without going through each and every individual cell. Cells with formatting will draw the viewer's attention to the important cells.

In Excel, formatting worksheet data is easy. You can use several fast and simple ways to create professional-looking worksheets that display your data effectively. For example, you can use document themes for a uniform look throughout all of your Excel spreadsheets, styles to apply predefined formats, and other manual formatting features to highlight important data.

Formatting a Data

Raw Data

 


Using Font, Number tabs as shown in image to do a simple formatting

 



Formatting a Data with help of formatting tools in Excel 

As shown in image , we have Product category wise data with Taxable value, Cost, Profit and Profit Ratio.

There are no gridlines, headers, formula bar and ribbon, these can be hidden with formatting tools.

 



In Below Image you can see Formula tab, header and gridlines

 

 

In Below image there is none. Untick gridlines, headings and formula bar.




Below is a Report prepared using various formatting tools like chart and Smart Art.



 

As shown above we can convert a simple data into a nice and presentable form with the help of formatting tools available in Excel.

As shown in image below, with this style of formatting and presenting a data there is no need to rework the data and show it in PowerPoint.

 



Types of Formatting 

Press Ctrl +1 or 

1. Numeric

I. Date formatting 

 


II. Special Formatting – Change Security code number formatting and Phone number formatting.

 




III. Custom Formatting 

You can custom a formatting as required

 


2. Display

In Home Tab – Font feature 

Change font of the text , size, Colour

Fill colours in cell

Apply borders

Underline, Bold or Italic a character

 




3. Tools

Justify option allows the text copied from internet or word to be changed.

 




Background – Change background from Page layout option

 




4. Row & Column

Data with no Row or column formatting

 



After adjusting row and column

 





5. Outlining

Data Tab – Outline -- Subtotal

 



After Outline

 






Grouping

Data Tab – Outline -- Group

 



6. Visualization

Sparklines – Insert High, Low, first, last, negative markers.

 







WordArt


 



Shape Effects


 



Themes

Pagelayout – Colours or Themes

 



7. Conditional

Home – Conditional Formatting – there are various options for formatting as shown in image. 

 



Conditional Formatting with Formula



 


Labels:

Comments

Post a Comment

Popular posts from this blog

Data Model an easy substitute for Vlookup

  you have a data set with product, date, customer, and sales information. The IT department forgot to put sector in there. Here is a lookup table that maps customer to sector. Time for a VLOOKUP, right? There is no need to do VLOOKUPs to join these data sets if you have Excel 2013 or newer. These versions of Excel have incorporated the Power Pivot engine into the core Excel. (You could also do this by using the Power Pivot add-in for Excel 2010, but there are a few extra steps.) In both the original data set and the lookup table, use Home, Format as Table. On the Table Tools tab, rename the table from Table1 to something meaningful. I’ve used Data and Sectors. Select one cell in the data table. Choose Insert, Pivot Table. Starting in Excel 2013, there is an extra box, Add This Data to the Data Model, that you should select before clicking OK. The Pivot Table Fields list appears, with the fields from the Data table. Choose Revenue. Because you are using the Data Model, a new line a...
Post a Comment
Read more

Update All Sheets at same time in a Excel Workbook

Say you have to urgently submit a file with many sheets. For Example put a Header or write a same  data for all sheets. I will show you an amazingly powerful tool called Group mode. Say that you have 12 worksheets that are mostly identical. You need to add totals to all 12 worksheets. To enter Group mode, right-click on any worksheet tab and choose Select All Sheets. The name of the workbook in the title bar now indicates that you are in Group mode. Anything you do to the January worksheet will now happen to all the sheets in the workbook. Why is this dangerous? If you get distracted and forget that you are in Group mode, you might start entering January data and overwriting data on the 11 other worksheets! When you are done adding totals, don’t forget to right-click a sheet tab and choose Ungroup Sheets.
Post a Comment
Read more

Improve your Excel Productivity with these Shortcuts and formulas

I have given below 45 tips and tricks to improve your productivity while working in Excel. Useful Keyboard Shortcuts 1.  To format any selected object , press ctrl+1 2.  To insert current date , press ctrl+; 3.  To insert current time , press ctrl+shift+; 4.  To repeat last action , press F4 5.  To edit a cell comment , press shift + F2 6.  To autosum selected cells , press alt + = 7.  To see the suggest drop-down in a cell , press alt + down arrow 8.  To enter multiple lines in a cell , press alt+enter 9.  To insert a new sheet , press shift + F11 10.  To edit active cell , press F2 (places cursor in the end) 11.  To hide current row , press ctrl+9 12.  To hide current column , press ctrl+0 13.  To unhide rows in selected range , press ctrl+shift+9 14.  To unhide columns in selected range , press ctrl+shift+0 15.  To recalculate formulas , press F9 16.  To select data in current region , press ctrl+shift+8 ...
Post a Comment
Read more