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

Turn Data Sideways

Someone built this lookup table sideways, stretching across C1:N2. I realize that I could use HLOOKUP instead of VLOOKUP, but I prefer to turn the data back to a vertical orientation. Copy C1:N2. Right-click in A4 and choose the Transpose option under the Paste Options. Transpose is the fancy Excel word for “turn the data sideways.” I transpose a lot. But I use  Alt+E ,  S ,  E ,  Enter  to transpose instead of the right-click. There is a problem, though. Transpose is a one-time snapshot of the data. What if you have formulas in the horizontal data? Is there a way to transpose with a formula? The first way is a bit bizarre. If you are trying to transpose 12 horizontal cells, you need to select 12 vertical cells in a single selection. Start typing a formula such as  =TRANSPOSE(C2:N2)  in the active cell but do not press Enter. Instead, hold down  Ctrl+Shift  and then press  Enter . This puts a single array formula in the selected cells. T...
Post a Comment
Read more

Rank Function

How to Use the RANK Function If you give the RANK function a number, and a list of numbers, it will tell you the rank of that number in the list, either in ascending or descending order. For example, in the screen shot below, there is a list of 10 student test scores, in cells B2:B11. To find the rank of the the first student's score in cell B2, enter this formula in cell C2: =RANK(B2,$B$2:$B$11) Then, copy the formula from cell C2 down to cell C11, and the scores will be ranked in descending order. RANK Function Arguments There are 3 arguments for the RANK function: number : in the above example, the number to rank is in cell  B2 ref : We want to compare the number to the list of numbers in cells  $B$2:$B$11 . Use an absolute reference ($B$2:$B11), instead of a relative reference (B2:B11)so the referenced range will stay the same when you copy the formula down to the cells below order : (optional) This argument tells Excel whether to rank the list in ascending or descending o...
Post a Comment
Read more

Data Cleaning Functions in Excel

  The CLEAN function Using the CLEAN function removes nonprintable characters text. For example, if the text labels shown in a column are using crazy nonprintable characters that end up showing as solid blocks or goofy symbols, you can use the CLEAN function to clean up this text. The cleaned‐up text can be stored in another column. You can then work with the cleaned text column. The CLEAN function uses the following syntax: CLEAN(text) The text argument is the text string or a reference to the cell holding the text string that you want to clean. For example, to clean the text stored in Cell A1, use the following syntax: CLEAN(A1) The CONCATENATE function The CONCATENATE function combines, or joins, chunks of text into a single text string. The CONCATENATE function uses the following syntax: CONCATENATE(text1,text2,text3,...) The text1, text2, text3, and so on arguments are the chunks of text that you want to combine into a single string. For example, if the city, state, and ZIP co...
Post a Comment
Read more