What is Macro and how to Create (record) a Macro and run a Macro in Excel?

A macro is an action or a set of actions that we can run as many times as we want. When we create a macro, we are recording our mouse clicks and keystrokes. If we have tasks in Microsoft Excel that we do repeatedly, we can record a macro to automate those tasks.

To Record a macro in MS Excel, do the following:- 

1. Open the spreadsheet.

2. Go to the “View” tab of the ribbon and click the tiny arrow below the “Macros” button under the Macros group. 

3. Then click “Record Macro" 

4. Type in the name of your macro and click “OK” to start the recording. The macro will be created. 

5. When you’re done, go to the “View” tab, click the tiny arrow below the “Record Macro” button again and select “Stop recording”.

To Run a Macro in MS Excel 

Once created, we need to run the macro. A list of macros can be accessed from the View tab. To view this list, do the following:- 

1. Select the View tab 

2. Click the button labelled Macros to bring up the Macro list 

3. Select the macro you want to run from the list, then click the Run button. You can also show the Macro List at any time by pressing ALT+F8.

Read More

What is Hyperlink and write the Procedure to create Hyperlink in MS-excel with Example.

 A hyperlink is text or an image within a file on your computer that you can click on that gives access to another document or image. Words on a website that are underlined and highlighted in blue and that you can click on in order to open a new web page are an example of a hyperlink.

We can use hyperlinks in spreadsheet too for the following:- 

1. Hyperlinks to websites 

2. Hyperlinks to an existing document 

3. Hyperlink to a new document 

1. Creating a Hyperlinks to websites:- we can create hyperlinks to websites for additional references, important email address etc. 

To create a hyperlink to a website, do the following steps:- 

1. Open a new worksheet in spreadsheet software. 

2. Place the cursor where we want the hyperlink to be displayed. Eg Cell A1 

3. Type the URL. Eg:- www.ecomputerconcepts.com 

4. The hyperlink automatically created and displayed. 

We can also modify and replace the hyperlink text.

2. Creating a hyperlink to an existing document (this sheet):- we can create hyperlink to documents available on the computer or another computer in our network. To create a hyperlink to a document, do the following steps:- 

1. Open a new blank worksheet. 

2. Place the cursor where we want the hyperlink to be displayed. Eg Cell A1 

3. Select the Insert Menu. 

4. Select the hyperlink option under insert menu. 

5. Select document option in the left pane under the hyperlink dialog box. 

6 click the folder icon, browse and select file and click open button. 

7 click apply and close button. Notice the hyperlink is created now click this link open this document.

3. Creating a hyperlink to a new document (external data):- This is useful when we want to create the document after the hyperlink. To create a hyperlink to a new document, do the following steps:- 

1. Open a new blank worksheet. 

2. Place the cursor where we want the hyperlink to be displayed. Eg Cell A1 

3. Select the Insert Menu. 

4. Select the hyperlink option under insert menu. 

5. Select New Document option in the left pane under the hyperlink dialog box. 

6. If we create a presentation and also embed a link to the presentation. 

7. Click the apply and close button. Notice the presentation is created and launched a new window for further editing.

Read More

What is Scenario and how to use Scenario in MS Excel?

A Scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

To use Scenario in MS Excel, do the following:- 

1. On the Data tab, in the Forecast group, click What-If Analysis. 

2. Click Scenario Manager. The Scenario Manager Dialog box appears. 

3. Add a scenario by clicking on Add button. The add scenario dialog box appears. 

4. Type a name of scenario and select Changing cells (A4 :A8) and click on OK. 

5. Enter the corresponding value and click on OK again. Finally, your Scenario Manager should be consistent in scenario dialog box. 

6. If we see the result of a scenario, select the scenario and click on the Show button. Excel will change the value of cell A4: A8) accordingly for you to see the corresponding result on the sheet.

Read More

What is Goal Seek Option and how to use Goal Seek option in MS Excel?

Goal Seek is a process of calculating a value by performing what-if analysis on a given set of values. For our purposes, Excel's Goal Seek feature lets you adjust a value used in a formula to achieve a specific goal. Or, put another way, Goal Seek determines input values needed to achieve a specific goal.

Use Goal Seek in MS Excel, do the following:-

1. Open the blank spreadsheet and enter some data and calculate the result. 

2. Put the cursor on the cell that contains the output you want to change. 

3. On the Data ribbon, select What-if-analysis, then select Goal Seek. 

4. When the dialog box appears make “Set cell” eg. (A10) 

5. Set “To value” equal to the output you want to achieve (type the number in) 

6. Set “By changing cell” equal to the assumption you want to solve for eg. cell E4 

7. Press OK.

Read More

What is Subtotals Function and How to Creating Subtotals in MS Excel?

The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database. The SUBTOTAL function is a built-in function in Excel that is categorized as a Math/Trig Function. 

It can be used as a worksheet function in Excel. The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data.

To create a subtotal, do the following:- 

1. First, sort your worksheet by the data you want to subtotal. 

2. Select the Data tab, then click the Subtotal command. 

3. The Subtotal dialog box will appear. 

4. Click the drop-down arrow for the Use function: field to select the function you want to use. Eg. Sum, count etc. 

5. When we're satisfied with your selections, click OK. When we create subtotals, our worksheet it is divided into different levels.

Read More

What is data Consolidation and how to Consolidating Data in MS Excel?

Data Consolidation allows you to gather together your data from separate worksheets into a master worksheet. In other words, the Data Consolidation function takes data from a series of worksheets or workbooks and summaries it into a single worksheet that we can update easily.

To Use Consolidate function, do the following:- 

1. Open all workbooks or sheets we want to consolidate. Ensure the data is organized in the same way 

2. Open a blank workbook. On the Data tab, in the Data Tools group, click Consolidate 

3. Choose the Sum function to sum the data. 

4. Click in the Reference box, select the range eg A1:E4 in the first workbook, and click Add. 

5. Repeat step 4 for the next workbooks. 

6. Check Top row, Left column and Create links to source data. 

7. Click OK. This adds the consolidated data to the blank spreadsheet or workbook.

Read More

What is Formula and how to enter formula in MS Excel?

 A formula is an expression which calculates the value of a cell. MS Excel to recognize a formula, it must start with the equal sign (=). 

To enter a formula, do the following:- 

1. Select a cell. 

2. Type the equal sign =. 

Note: Formulas in Excel always begin with the equal sign.

 3. Select a cell or type its address in the selected cell. 

4. Enter an operator. Eg (+,-,/,* etc). 

5. Select the next cell, or type its address in the selected cell. 

6. Press Enter.

Read More

What is Password Protection and how to use it MS Excel?

To prevent others from accessing data in your Excel files, protect your Excel file with a password. It allows only those with an authorized password to gain access to certain information. 

To Use password protection, do the following:- 

1. Select File Menu and select option Info. 

2. Select the Protect Workbook box and choose Encrypt with Password. 

3. Enter a password in the Password box, and then select OK. 

4. Confirm the password in the Reenter Password box, and then select OK.

Read More

How to Sorting and Filtering data in MS Excel?

1. Sorting Data:- 

Sorting data in MS Excel rearranges the rows based on the contents of a particular column. We may want to sort a table to put names in alphabetical order. Or, maybe we want to sort data by Amount from smallest to largest or largest to smallest. Select the Column by which you want to sort data. 

1. Select a cell in the column we want to sort (a column with numbers). 

2. Click the Sort & Filter command in the Editing group on the Home tab. 

3. Select From Smallest to Largest. Now the information is organized from the smallest to largest amount. 

2. Filtering Data:-

 Filtering is a quick and efficient method where we display only that data that meets a given criteria. Ms excel uses two types of filtering methods, that is, Auto filter and Advanced filter. 

1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. 

2. Click the drop-down arrow for the column you want to filter. 

3. The Filter menu will appear. 

4. When you're done, click OK. 

5. The worksheet will be filtered according to your search term.

Read More

What are the applications of Spreadsheet?

 A spreadsheet is a computer application for organization, analysis and storage of data in tabular form. Today, Microsoft Excel is the most popular and widely used spreadsheet program, but there are also many alternatives. The various application used to create a spreadsheet. 

1. Google Sheets - (online and free). 

2. Libre Office 

3. Lotus 1-2-3 

4. Microsoft Excel. 

5. OpenOffice

Read More

Explain the basic Microsoft Excel terminology.

 Workbook — A workbook is the name given to an Excel file and contains one or more worksheets. Although you're limited to 255 sheets in a new workbook, Excel doesn't limit how many worksheets you can add after you've created a workbook. 

• Worksheet — A Worksheet is a collection of cells organized in rows and columns. A worksheet or sheet is a single page in a file created with an electronic spreadsheet program such as Microsoft Excel or Google Sheets. 

• Cell — A cell is a rectangle or block housed in a worksheet. Any data that you want to enter into your worksheet must be placed in a cell. An Active Cell is one that is currently opened for editing. 

• Columns and Rows — Columns and Rows refer to how your cells are aligned. Columns are aligned vertically while rows are aligned horizontally. 

• Ribbon — Above the workbook is a section of command tabs called the Ribbon. A multitude of options are found behind each tab of the ribbon 

• Cell Reference — A cell reference is a set of coordinates that identifies a specific cell. It's a combination of letters and numbers. A5, for example, would point to the cell located where column A and row 5 intersect. 

• Cell Range — A Cell range is a collection of cells that have been identified as a group based on a variety of criteria. By using a colon (:) between cell references. for example, A1:C1.

Merged Cell — When two or more cells are combined, it's become what is known as a merged cell. 

• Formula — A sequence inside a cell that is used to produce a value. It must begin with an equal (=) sign. This could be a mathematical equation, cell references, functions or operator. A formula is also known as an expression. 

• Formula Bar — The Formula Bar will display the contents of an active cell. In the case of formulas, the formula bar will display all components of the formula. Function — Functions are formulas that are pre-built into Excel. They are designed to help simplify potentially complex formulas in a worksheet.

Read More

What is spreadsheet?

 A spreadsheet or worksheet is a file made of rows and columns that help sort data, arrange data easily, and calculate numerical data. The three most common general uses for spreadsheet software are to create budgets, produce graphs and charts, and for storing and sorting data. Microsoft Excel, a program in which you enter data into columns, is an example of a spreadsheet program.

Read More