How to do Conditional Formatting in Excel

You might have come across a situation in which you need to analyze a huge data in Excel within a short time frame. That makes you worried how difficult it could be to analyze thousands of Excel rows with data values and present a final report. Well, there is no need to worry because we are going to tell you about a great feature of Excel called as Conditional Formatting which is going to make your data analysis a breeze.

Let us take an example of a University which has conducted an entrance exam for Management Courses. Below sheet represents the students Application number along with Name, High School Percentages, Certification Exam Score, Entrance Test Score, Sponsorship Status and whether they are applying for scholarship or not.

Refer below image

Now you need to present analysis for the data to the Selection Committee so that they can make a wise decision and publish the results accordingly. To do manual analysis over the data is surely a time-consuming task and might not be accurate. So, we will use the functionality of Conditional Formatting in Excel to bring out various analysis results out of the data. Check the various case scenarios explained below.

To find out the names of all Women Candidates in list who are married (Having Title – Mrs.)

For finding all Married Women candidates from the list we will select the data range from Cell B3:B25 and click on Conditional Formatting under Styles Group in Home Tab. Further click on “New Rule” which will open a pop up box.

Refer image below

Now you will observe different Rule Types. Select the Rule Type “Format only Cells that Contain”. In the section of Edit the Rule Description change the value to Specific Text in the first dropdown, beginning with in the second and our Text “Mrs.” In third dropdown.

Refer below image

Now, you need to set the formatting option. Click on the format box and select the color to highlight the data post application of defined condition and select Italics under Font section. You can also define other formatting options such as making text Bold, Effects, Border etc. Once you have completed the formatting options Click on OK.

Refer below image


Now all those Candidates will be highlighted having Title as “Mrs”. At the beginning of their name.

Note – We did not used the conditional formatting of “Text Contains” since in that case Cell B17 would also become highlighted which does not contain title but similar keywords.

Refer below image

Let us understand another case for finding out the highest scores from each of the Qualifying Exams.

To find out the Top Five High Scores in list.

For finding the Top five scores in respective exams we will use Conditional formatting. Select the data range from C3:C25 and click on Conditional Formatting and New Rule.

Now select the Rule type as “Format only top or bottom ranked values” and change value of drop down to Top and value to 5 in Edit description section. Now select the formatting options from the last section as desired.

Refer below image

On clicking Ok Excel will highlight the Top Five scores under High School Percentage Column.

Refer below image

Similarly, we can find out the top five scores in Certification and Entrance Test by applying conditional formatting and choosing a different color scheme to identify each data uniquely.

Refer below image

Note – In the above image for column D there are seven highlighted entries since multiple candidates have scored same number.

Let us consider a case where we need to highlight scores more than 90, scores in the range of 60 to 89 and scores less than 60.

To utilize the icon set feature of Conditional Formatting.

We will use the icon sets for highlighting the score ranges by selecting all the data range. Select Cell range C3:E25 and click on Conditional Formatting – icon sets menu item – More Rules.

Refer below image

Select the Rule Type as “Format all cells based on their values”, format style to Icon Sets and Icon Style to the desired style, set the range indicators and click Ok. For our case, we will put value as >=90 and type as Number for Right Icon, for exclamation icon we will put value as <90 and >=60 – type – Number and for Wrong it will consider all values <60.

Refer below image

Observe the conditional formatting of icon set which clearly highlights the scores as per the defined criteria.

To Highlight the Data Value of a Column.

Let us now take a scenario in which we need to highlight the data value of a column. Suppose, we need to highlight all the candidates which are industry sponsored. We will create a new rule and in the Rule Type select “Format only cells that contain”, select “Cell Value”, Select “Equal to” and “INDUSTRY SPONS” in the edit section. Choose desired formatting and apply.

Similarly, we can highlight all the candidates who have applied for Scholarships.

Refer below image

To remove the Conditional formatting choose Clear Rules then select among Cells or Sheet. You can also manage the Conditional Formatting Rules from the Manage Rules, Change Rule priority and delete the rules.

Refer below image


Conditional formatting menu has several predefined conditions ready for use such as Top 10, Below 10, Average, Duplicates, Date Occurring, Greater than & Less than etc.



Using Data Bars and Color Schemes.

To highlight the value of cells by usage of bars we will use the Data Bars under Conditional Formatting.

Select the data range and type of Data Bar you will observe that selected cells gets filled up with the data value bar indicating the cells with higher or lower values.

Similarly, you can use the Color scales on the selected data to color cells within a range of defined criteria based on Lowest value to Highest, Number, Percent or Formula.

Refer below image

So, after doing several case analysis we can easily deduce many information from the data set such as Name of Married Women Candidates, Candidates who have failed in all three tests based on the criteria, Top scores achieved, Candidates with Industry Sponsorship etc. It is a very useful feature of Excel which quickly lets you find out any variance in data range or simplify your data for further analysis or presentation.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

How to Combine Cells in Excel

You might be wondering what’s the need of combining cells in Excel when one can simply type the data of both cells to a new Cell. Well, we would say yes you can do it but suppose you have a large dataset consisting of thousands of rows then it would not be an easy task to manually type in the entire data from each cell.

So, to avoid all the manual effort of typing the data we are going to tell you the process of Combining Cells in Excel by using the ampersand sign “&” or by using the formula of Concatenate Cells.

Let us Understand with Example Case

Now let us consider an example case of a Housing Society’s Data. List consists of Name, Surname, Apartment Name, Block, Room No. of all the residents in the Society.

Refer below image


Suppose we need to combine the Name and Surname of the resident in Cell B3 and C3 to a new Cell G3. We will write the formula as =B3&C3 (click on Cell B3, put ampersand sign, click on Cell C3).

This will result into combination of text as “NicolasTheoder” but there is no space in between the text to make it presentable.

Adding Spaces between Combined Cells.

So, to make it readable we will write the formula in Cell G3 as

=B3& ” ” &C3 (click on Cell B3, put ampersand sign, put double quotes with space, ampersand sign and click on Cell C3).

This will result into combination of text as “Nicolas Theoder” (with proper space in between the texts).

Refer below image

Now if you want to combine the names and surname for all entries you do not need to type the formula again. Just hold the cell G3 from bottom right corner and drag towards cell G12. This will copy the formula to all the cells along with corresponding cell references.

Refer below image

Adding text/punctuation in between combined Cells.

In case you wanted to combine the Names & Surname with comma or semi colon or dash in between you may write the formula as

=B4&”,”&C4 or B4&”;”&C4 or B4&”-“&C4 respectively.

Refer below image

Combining Cells along with Custom Text

If you want to combine Cells of Name and Surname along with Salutation in Column H. Then you need to write the formula as

=”Mr./Ms.”&B3&” “&C3 (put = sign on cell H3, put double quotes with text Mr./Ms., put &ampersand sign, click on cell B3, put ampersand sign, put double quotes with space – to give space between name and surname, put ampersand sign &, click on Cell C3).

Refer image below

Combining Multiple Cells

Consider the scenario in which you need to combine Apartment Name, Block and Flat no. from Cells D3:E3:F3 to Cell I3 (Address Column). We will use below formula to combine

=D3&” “&E3&F3

(put equal to sign in Cell I3, Click on Cell D3, put ampersand sign, put double quotes with space, put ampersand sign, click on cell E3, put ampersand sign, click on cell F3).

You can drag the formula to fill in the cell range to I12.

Refer below image

Combining Cells along with Custom Text Before and After Combined Cells

Now consider a scenario where we need to add Custom Text before and after the combined cells. We will combine a custom text “The Address of” with Cell Value H3, Custom Text “is”, Cell Value of I3. We will click on Cell J3 and write formula as

=”The Address Of “&H3&” “&” is “&I3

(put equal to sign in cell J3, enter the custom text in double quotes with space at end, put ampersand sign, click cell H3, put ampersand sign, put double quotes with space, put ampersand sign, type custom text within double quotes with a space at end, put ampersand sign, click on cell I3).

Drag the formula to cell range I12.

Refer below image

Combining Cells along with Formula

Now let us consider a scenario where in we need to combine two cells with one having a formula. In that case, also we will simply use the ampersand sign to combine both cells and result will be displayed accordingly.

Combining Cells Using Concatenate Function

Excel has a built-in function to combine cell values by using function “Concatenate”. You need to put equal to sign in the cell where you want to combine the cells and then type concatenate followed by cell separated by comma within brackets. As you press enter the cell will be combined. You can multiple cells in the same manner.

If we have used the function for combining Surname along with name, then formula would be

=CONCATENATE(C4,B4)

Similarly, if we want to put space between concatenated texts then we will use ampersand sign in between the formula. The formula would look as below =CONCATENATE(C4&” “&B4).

Hope you understand this feature now and do not forget to try out yourself for sample cases as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

How to Add Up a Column of Numbers in Excel

You might be surprised to know that major use of Excel spreadsheet is only done for summation of large data ranging across all levels of Organizations, Business and Personal Lists.

Adding up of cells can be an important part of data analysis where in your cell contains some sales figure or expenditure or credit/debit bills etc. So, let us start learning summation process of columns in the simplest way.

Assume you have a data record of Students Marksheet as shown below in image


Column A shows the Subject Names and Columns B,C,D,E,F,G shows students marks respectively.

Now to sum up each student marks we will add up the numbers present in respective Columns of student. We will use the below formula in Cell B11 for adding of numbers in Column B

=B4+B5+B6+B7+B8+B9+B10

Similarly, for adding the numbers in Column C we will use below formula in Cell C11

=C4+C5+C6+C7+C8+C9+C10

Refer below image showing formula usage in Cell B11


Subsequently similar formula can be written for other columns summation where in Column letter will change and Cell range would be from 4:10

Excel takes care of any modification in the cell ranges defined, in this case if there will be any change in students’ marks then the formula would instantly reflect the new total. So, you do not need to type in the formula again and again.

So wasn’t it an easier method to add up the numbers in a column?

Alternative to above method of writing Cell ranges to add up you can also click and define them by below method.

Select the Cell B11 put an equal to symbol and now click on cell C4 then press the + symbol now again click on Cell C5 then + symbol till you reach the end of range.

But you might be wondering what if I have multiple records do I need to type in the formula again and again? Well the answer is No. Excel itself is a smart software having many in built features. You only need to write down the formula once let’s say for Cell B11 only and then hold the bottom right corner of the cell and drag towards Cells – C11, D11, E11, F11, G11.

Refer image showing applied formula in Cell C11


This will copy the summation formula you typed to these cells and auto arrange the reference cells. Which means that Summation of cell C11 will add numbers from the range C4:C10 only irrespective of the fact that the formula was copied from B11 cell defining range of B4:B10.

Isn’t it a smart feature!

Now let’s go to next level – What if I have thousands or Lac’s of records for addition, do I need to type in the addition formula for first column comprising of thousand entries?

Well the Answer is again NO. For handling, large volumes of data addition excel has a built-in function called as SUM. This function can be called by selecting the cell and put equal to symbol followed by SUM with brackets for defining ranges of data to be added.

Now let us use this formula in our previous case. We will select cell B11 and put equal to symbol along with function SUM defining range from B4:B10

=SUM(B4:B10)


On pressing enter Excel will add up the numbers in column B. Similarly, we can use this function for all other Cell ranges to add up the student’s marks. Alternatively, you can also drag the formula to all relevant cells for auto summation.

Another interesting feature of addition function in Excel is that you can define any range while writing the formula or using the SUM function. Refer below image for example.

Cells ranges have been defined from each of the columns and the Excel adds up each cell value as desired.


Excel gives another smart feature of Auto Sum for those who find typing formula cumbersome or difficult.

To use the Auto Sum feature, select the cell you wish to calculate the sum in and then click on Auto Sum button present under Home Toolbar (highlighted). Function of SUM will be automatically applied to the given cell for entire table range.


If you want to select specific cells in a column for addition, then select those cells and then press Auto Sum button.

If you want to apply the Auto Sum function to complete numeric data, let’s say in our case we need to calculate the Total Marks of all students at once then select Cell ranges from B4 to G10 and press the Auto Sum button.



All columns will display the total marks below the selected range cells i.e. in Cell Range B10:G10

Hope you understand this feature now and do not forget to try out yourself for sample cases as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

How to Blank Out Cells in Excel

You might be wondering what is the need to Blank out cells in excel when you can simply use the work area in sheet and ignore rest of the cells. Well the answer is you do need to blank out the unused cells in excel when you do not want that users should enter any data or information into cells other than defined work area or you wish to conceal your sensitive information or formula.

So, to blank out the cell completely leaving the work area select the row from where you want to blank out cells and Press Ctrl Key + Shift key + Up/Down Arrow this will select all the rows in excel. Now Right Click on any row and click on Hide. In the same way select the column from where you need to blank out cell press Ctrl Key + Shift key + Right/Left Arrow. This will blank out all the cells and you work area will be the only highlight. You can also select the Hide option from the Home Toolbar, Cells Group- Format button. Clicking on the format button opens the menu which has a visibility section with a sub menu to Hide Rows or Columns. This will have the same action as selecting Hide from the Right Click menu.

Refer below image


 

You can also blank out empty cells in Rows and Columns by reducing the default height or width to zero for selected rows and columns. To blank out cells in selected row click on Format button in Home toolbar under Cells group. Now select the Row Height entry under cell size heading. You will observe below screen. Just enter 0 and press Ok. Your selected cells of Row will be reduced to 0 height which is like using Hide Row option. Similarly, to blank out cells of a column you can reduce the Column width to 0 and achieve the same results using Hide Columns option.

Refer below image


 



 

 

Now Let us understand this with an example of a University in which you need to fill in a form in excel get it printed, approved and submit to the Administrative Department.


The form has a small work area and lot of unnecessary rows and columns which makes the work area non-focusable. Some students might also start typing text or requests in empty area which might create issues in alignment of text while printing.

To keep the students focused on form and avoid any use of space outside work area we will Blank out the cells from each of the unused Rows and Columns. Select Column K since our work area is till column J only and press Ctrl Key + Shift Key + Right Arrow Key this will select all the cells till it reaches the end column of a worksheet. In case you want to blank out selected columns only then you can manually select the columns as desired.

Refer image below


Now Right click on any column this will open the menu screen and select Hide from the Menu screen.

Refer image below

 


 

On clicking Hide option all the selected columns will get hidden and you will be presented with below screen.

Refer below image


Now select Column C and press Ctrl Key +Shift Key + Left Arrow key to select all cells from Column A to C

Right click to get the menu and select Hide option. You will observe that now all the columns are hidden and cells blanked out.

Refer image below


 

Now to blank out the empty cell in Rows we will follow the similar procedure. In this case we will select Row 3 since below this row our work area starts and then press Ctrl Key + Shift Key + Up Arrow Key this will select all the rows from 1-3. Now right click on any row and select the Hide Option from the Menu.

You will observe that empty cells above the work area are now blanked out.

Refer below image


 

Now we are left with empty cells of rows down to work area. So, we will select the Row number 21 since it is the row just after the last workspace row and then press Ctrl Key + Shift Key +Down Arrow Key.

This will select all the Rows till the end of limit of worksheet. Now right click on any row and select the Hide option from the menu.

Refer below image

 


You will observe that all unwanted cells are now blanked out and you have only your workspace area left on the worksheet.

Refer below image


 

Now our form is well focused on the worksheet and there is no empty cell to write any unwanted text.

 

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!


 

How to Break Links in Excel

You might have come across a situation in which you maintained an external link to another Excel Workbook and somehow that Workbook gets deleted or replaced or that information is no longer required to be updated. Then every time you open the Workbook it shows the Broken Link Error.

You might be thinking how to fix the same?

So, in that case, you need to break the links in Excel in your original file.

Breaking existing links in Excel 2016 can be achieved by selecting Data Tab on the Toolbar and then clicking on Edit Links (highlighted) in Connections group. Refer below image


Once you will click on Edit Link Excel will show up all the external references used in the Workbook.

You can select any of the source and click on Break Link. If you need to select all the links at once press Ctrl + A else hold the Ctrl Key and select multiple links as required.


The Edit Links button will appear disabled if there are no external references present in your Excel Workbook.

If you have defined a name for the link in your Workbook, then on breaking the link the defined name will not get removed automatically. You need to remove it separately by Name Manager button present under Formula tab – defined names section.

Click on the Name Manager will open the dialog box as shown in image below with all the Links defined with a Name. You need to select the entry and click on Delete button (highlighted)


Let us now understand this process by referring to an example of Worksheet Links. This will be useful for those who are new users to Excel and still in the learning phase.

Consider a case of an IT Company where a Manager needs to update Total Project Value by referring Project Value Realization Sheet. The data of Project Value Realization Sheet is linked with Cost Savings Sheet. Whenever any employee updates the Projected Cost in Savings Sheet it reflects in Project Realization Sheet.

Refer below image of a folder containing specified files


Project Realization Sheet contains month wise breakup of the savings figure along with total savings achieved. Note – The External Reference link (highlighted) in image below to the other workbook named – Cost Savings Sheet in the Cell D6 and similarly for Cells D7 and D8 respectively.


Cost Savings Sheet contains Project wise monthly saving data which is being updated by different employees. It also contains the Total savings for all the months in Cell F6 which is an external Reference to Cell D10 of Project Value Realization Sheet.


Both the files are at a specific location inside a folder. Suppose if the Cost Savings file gets moved to other location or deleted. You will get below message while opening the Project Realization Sheet.


Either you can continue or try editing the links. Once you click on Edit Links you will observe below screen showing Error: Source Not found


Now to remove this error message completely from appearing every time you open the Workbook you need to Break Link. Observe the button of Break Link in above screen. Once you will click on Break Link, system will give you a warning that any external references will be permanently converted to their existing values. This means that the data will no more get updated once you change the referenced sheet.


On clicking Break Links, Excel will remove the selected reference from Workbook. Observe the Cell D6 contains no external reference now. Refer below image


In this way, you may Break as many links present in a Workbook by simply selecting the links or selecting all the links at once.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

You might have come across a situation in which you maintained an external link to another Excel Workbook and somehow that Workbook gets deleted or replaced or that information is no longer required to be updated. Then every time you open the Workbook it shows the Broken Link Error.

You might be thinking how to fix the same?

So, in that case, you need to break the links in Excel in your original file.

Breaking existing links in Excel 2016 can be achieved by selecting Data Tab on the Toolbar and then clicking on Edit Links (highlighted) in Connections group. Refer below image


Once you will click on Edit Link Excel will show up all the external references used in the Workbook.

You can select any of the source and click on Break Link. If you need to select all the links at once press Ctrl + A else hold the Ctrl Key and select multiple links as required.


The Edit Links button will appear disabled if there are no external references present in yours Excel Workbook.

If you have defined a name for the link in your Workbook, then on Breaking the Link the defined name will not get removed automatically. You need to remove it separately by Name Manager button present under Formula tab – defined Names Section.

Click on the Name Manager will open the dialog box as shown in an image below with all the Links defined with a Name. You need to select the entry and click on Delete button (highlighted)


Let us now understand this process by referring to an example of Worksheet Links. This will be useful for those who are new users to Excel and still in the learning phase.

Consider a case of an IT Company where a Manager needs to update Total Project Value by referring Project Value Realization Sheet. The data of Project Value Realization Sheet is linked with Cost Savings Sheet. Whenever any employee updates the projected Cost in Savings Sheet it reflects in Project Realization Sheet.

Refer below image of a folder containing specified files


Project Realization Sheet contains month wise breakup of the savings figure along with total savings achieved. Note – The External Reference link (highlighted) in an image below to the other Workbook named – Cost Savings Sheet in the Cell D6 and similarly for Cells D7 and D8 respectively.


Cost Savings sheet contains Project wise monthly saving data which is being updated by different employees. It also contains the Total Savings for all the months in Cell F6 which is an external Reference to Cell D10 of Project Value Realization Sheet.


Both the files are at a specific location inside a folder. Suppose if the Cost Savings file gets moved to other location or deleted. You will get below message while opening the Project Realization Sheet.


Either you can continue or try editing the links. Once you click on Edit Links you will observe below screen showing Error: Source Not found


Now to remove this error message completely from appearing every time you open the Workbook you need to Break Link. Observe the button of Break Link in above screen. Once you will click on Break Link system will give you a warning that any external references will be permanently converted to their existing values. This means that the data will no more get updated once you change the referenced sheet.


On clicking Break Links Excel will remove the selected reference from Workbook. Observe the Cell D6 contains no external reference now. Refer below image


In this way, you may Break as many links present in a Workbook by simply selecting the links or selecting all the links at once.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

How To Add Text After A Formula In Excel

You might think that creating and using a drop down list in Excel is only something for the “experts” because it’s too complicated. Actually, creating and choosing items in a drop down list can be really easy, and there’s no programming knowledge needed. Today we’re going to show you how you can easily and simply create a drop down list in Excel and some practical ways you can use them.

Why would you want to create and pick items in a drop down list?

Drop down lists are really helpful when you often enter the same information in a column. Let’s say you keep track of your bills in Excel, and you have a column for whether or not it’s been paid. You could use the values of Not Paid, Payment Sent and Cleared. You can use drop downs when tracking elements of a project or work that you need to get done. Or, you can use it for the kids’ names on a chore list. Drop down lists can make using your spreadsheet faster, can keep you more organized and can reduce typing mistakes.

Drop down lists are actually one of the most simple tools to use in Excel, but they are more of a hidden feature since they can’t be accessed by a simply labelled single click. This is why so many people haven’t attempted to use them, instead typing the entries to each cell when they could make their lives so much easier with drop downs.

This feature is useful in business settings, for simple home management, financial planning and especially for entrepreneurs or freelancers who tend to have projects with many moving parts.

When you want a breakdown of your data in a visual format, you can use your drop down entries to create a pivot table. It is pretty easy too, and we’ll cover that in another article.

Creating your first drop down list

Be patient with yourself for the first few times you try this. The steps aren’t intuitive until you get used to it, but when you do, it will take you less than three minutes. We’ve tried several methods to create drop down lists and this one is the easiest since it involves the least amount of manual typing or different commands that you may be unfamiliar with.

We’ll make the drop down list with the Data Validation tool.

To begin, enter the values you want in your drop-down list in one column in a sheet. We are going to use the kids’ chores as an example here. By the end of this tutorial, you’ll be able to create your own drop down lists and duplicate them.

Next, highlight all items in the chores you have listed and name the list. Naming the list is done by simply clicking the box that says A13 in the image above and Chores on the image below and typing in the name of your list, in this case, Chores.

Next, select the cell where you want your drop-down list to be. After it’s selected we’re going to go to the Data Validation option in the Data tab. After you choose Data Validation, you will see a dialog box like the one below.


In this box, you will then be able to choose your “Validation criteria.” For this example, choose List from the selections.

The next step is to use the Chore list name that we created for the cells that had the chore names in them. Make sure to UN check the box that says Ignore Blank. The box with In cell Drop down should remain checked, since this is how you get to use this cool feature.

Simply type = Chore in the Source box, as in the example in the below image.

You can even add a message to prompt yourself or others to choose an option. This is done in the Input Message tab. It allows for a long message, but remember the small space that it will be displayed in when you type out what you want it to say. The display box will grow if more text is added, in the case of creating instructions, or more extensive notes. For this project, let’s keep it short.


You’ll also have to type Chore in the Title box to make sure it displays correctly.

Now click OK!

You can see in the below image that the drop down box has been created and when the cell that contains it is selected, the desired message is displayed in a small yellow box. Note that the small arrow to the right of the cell that allows you to choose options is only visible when the cell is selected.

What’s awesome is that you only need to go through that process once if you’re going to re-use the same list. You can simply select that cell that has the drop down list in it, copy it and paste it in another cell.

When you’re ready to assign the tasks to the kids, select the cell that the list is contained in. After it’s selected and you can see the little arrow to the right of the cell, just click the arrow. This will bring up all the selections in the list. You can choose any of the options that are available, but once you click the arrow, you can’t leave the cell blank unless you included a blank cell in your previous list of chores.

You can get creative in any way you’d like with your drop-down lists. This is an excellent way to have your response options already filled in and all you need to do is make one click.You can even have multiple lists on one spread sheet. For example, you can create another list of how well the kids did their chores, like Great, Done and Please Redo.

When you’re ready for a more advanced trick (it’s not that advanced!) you can make your lists on another tab in your workbook, so the original list is no longer visible on the same page as where you have your drop-down lists.

How to Create a Spreadsheet in Excel

You might have heard a lot about Microsoft Excel software or seen financial or business analysts working on the same making you think that it’s a tough thing to conquer. Well, if you think so it is absolutely wrong even you can be the master of Excel by using Microsoft Support or referring to our online Blog posts along with some self-practice.

Microsoft Excel is the most widely used spreadsheet software across the world. You need to have a Microsoft Excel software license or if you are a new user you can download Excel software – trial version for a limited period along with a Computer running Microsoft Windows. We will be using Microsoft Excel 2016 version for this post.

So, are you ready to create your first Spreadsheet?

Now let us begin our tutorial for step by step activities involved in the process. Firstly, you need to open the Microsoft Excel Application from the start menu or you can even type command excel.exe in the Windows Run menu which can be enabled by pressing Windows key + R

Refer below images



You will observe that Excel application is starting up for use and you will get the welcome screen displaying different workbook templates.



You need to select Blank Workbook from the available templates and you will observe a blank spreadsheet with Columns and Rows defined.


Columns are labelled with capital letters A, B, C, D and so on (Refer red box in below image) whereas Rows are labelled with numeric 1,2,3,4 and so on (Refer Green box in below image) till last limit of the spreadsheet.

Refer below image


The empty space seperated by grid lines are called as Cells. Each cell is uniquely identified by its address consisting of Column Name and Row Number. The first cell in spreadsheet is identified as A1 where in A represents Column A and 1 represents Row 1. Similarly, in the above image highlighted cell will be termed as E8. You can also refer the cell address of any cell by selecting it and refering the field above column A.

If you want to create another spreadsheet then you need to click on File menu and select New from the menu. You will get the same Welcome Template screen from where you can select the Blank Workbook or desired template.

Refer below image


On the upper side of the spreadsheet you are provided with a Toolbar having various tabs such as Home, Insert, Page Layout, Formulas, Data, Review and View. Each Tabs are further divided into Groups. Each Group has it’s own set of buttons required for different features.

Refer image below


Check the first group in Home tab – Name of Group is Clipboard and it contains tools such as Paste, Cut, Copy and Format Painter. Similarly, other tabs have their own set of groups and functionalities.

Now let us proceed with entering some data on our newly created spreadsheet.

You can prepare your grade sheet on Excel. Type in Name at Cell A1, DOB – Cell B1, Class – Cell C1.

Mention your name, date of birth, class in cells A2, B2, C2 respectively. Now in Cell A5 type Subjects followed by subject Names from Cell A6:A9. Enter the heading as First Sem, Mid Sem, Final Exam in cells B5, C5, D5 respectively. Enter Marks in cell range B6:D9 for each term exams.

Refer below image


You can observe that some of the text (Subject Names) is overlapping to column B. So, in this case you need to increase the column width by simply dragging the line between Column A and B.

Refer below image


Now if you want to total your marks enter below command in the cell B10

=B6+B7+B8+B9

This will add up all the values in defined cell. Similarly write command in Cell C10 ( =C6+C7+C8+C9) and D10 ( =D6+D7+D8+D9) to get total of each term exams.

Refer below image


So, you prepared your first spreadsheet and now you need to give a name to your spreadsheet and save it on your computer. Click on the File tab and select Save As. You will observe the screen showing options to save it in One Drive or on your local PC marked as This PC. Select the option of This PC and desired folder to save file, give your file a name such as My First Spreadsheet and click on Save.

Refer below image


The name of your spreadsheet will appear on the top of the Toolbar (Highlighted).


So now you are Ready with your first Spreadsheet!!

Hope you understand the process now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!