Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

Formats in Excel

What are the formats?

Formats are different options that one uses to change the appearance of data (maybe text, number, etc..) in an excel file. They do not change the value of the cell but change the way it appears. Like, coloring a cell will not change the value in the cell, similarly, changing the format of a numeric value to Numbers and adding decimals, will not change the value of the cell but just the way it appears to us.

What are the different ways by which formatting can be done?

There are three different ways by which formatting can be done -

  • Formatting options on Home group
  • Right-clicking of the mouse on a cell
  • Format Cell Dialog Box

Formatting Options on Home Group

Formatting Options on Home Group

We have already discussed the formatting options in the previous modules, so will not be moving with the base here again, apart from the Number options and Format Painter –

Number Options

Suppose you have budget numbers in some cells, but right now you do not have any $ or £ signs in front of them, so you will –

  • Select the cells that contain your data
  • Under the Home tab, click on Number group on Home tab
  • From the drop-down and select the currency option.

Once you select a currency, a currency sign will appear below the drop-down, you can then select the relevant currency for your salary numbers.

Number Options

Similarly, you can format dates if you want them in a particular way, you can convert numbers into a percentage.

Format Painter

Format Painter feature in Excel makes it easy and quick to copy the formatting of any cell or ranges and apply it to other cells or ranges into any worksheet or workbook. This is very similar to Copy & Paste feature, however, copies only formatting such as font type, font colour, text size, alignment, border type, and background colour.

Format Painter is a very useful tool and comes to use when you want to replicate the format in a particular cell to another cell –

  • Select the cell whose format you want to copy.
Select the cell whose format you want to copy.
  • Then do a left click on Format Painter under Home tab
Then do a left click on Format Painter under Home tab
  • Go to the cell where you want to have the same format and just do a left-click.
Go to the cell where you want to have the same format and just do a left-click.

Note: If you just do a single click on Format Painter, you will be able to format a group of cells that are together or only one cell. For changing the format of two or more cells that are not together, just do a double click on Format Painter, and you can go to all the cells one by one and change their formats. Once the changing formats have been finished, just click on the ‘Escape’ key on your keyboard.

Right-Click of the mouse

Right-Click of the mouse

When you do a right-click on a cell, the above menu appears. The formatting options are the same as that of the Home tab.

Format Cell Dialog Box

When you do a right-click of the mouse, apart from the menu seen above, you will also see the below menu. To use the formatting options, click on Format Cells.

To use the formatting options, click on Format Cells.

On clicking on Format Cells, the following box will open. It has different types of Formatting groups –

Numbers – when you want to format the value in a cell like adding a currency symbol in front of salary of employees, or adding commas in a number or removing decimal points from a number or changing the appearance of a date in excel file

On clicking on Format Cells,

Alignment – When you want to change a cell to left align, center align, top align, or even within a cell, if you want to tilt the text. You can also merge 2 or more cells together.

You can also merge 2 or more cells together.

Font – When you want to change the font of the cell, font size, font style (bold, un-bold, underline, strikethrough)

 When you want to change the font of the cell

Border – When you want to create a border around your cell or a group of cells. You can change the thickness of the borders as well by selecting the different options available.

Border – When you want to create a border around your cell or a group of cells.

Fill – When you want to colour the cell and create any pattern – by default the pattern is blank.

Fill – When you want to colour

Adding a Background Image to a Worksheet

You can also change the background of an excel file like what you do for your computers, tablets.

To add a background to a worksheet –

  • Go to the Page Layout tab
Go to the Page Layout tab
  • Then go to the Page Setup group and click on Background.
Then go to the Page Setup group and click on Background.
  • Excel will prompt you to select folders from where to select the picture – once done, click on Insert.
Excel will prompt you to select folders from where to select the picture