Excel formulas and functions

Excel formulas and functions

Formulas and functions are the bread and butter of Excel. They drive almost everything interesting and useful you will ever do in a spreadsheet. This article introduces the basic concepts you need to know to be proficient with formulas in Excel. More examples here.

What is a formula?

A formula in Excel is an expression that returns a specific result.

What is a function?

Working in Excel, you will hear the words "formula" and "function" used frequently, sometimes interchangeably. They are closely related, but not exactly the same. Technically, a formula is any expression that begins with an equals sign ).

A function, on the other hand, is a formula with a special name and purpose. In most cases, functions have names that reflect their intended use. For example, you probably know the SUM function already, which returns the sum of given references.

Excel Functions

1 Count and Sum: The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.

2 Logical: Learn how to use Excel's logical functions, such as IF, AND, OR and NOT.

3 Cell References: Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.

Basics of Excel

1 Ribbon: Excel selects the ribbon's Home tab when you open it. Learn how to use the ribbon.

2 Workbook: A workbook is another word for your Excel file. When you start Excel, click Blank workbook to create an Excel workbook from scratch.

3 Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

Excel INDEX Function

Index Function in excel is used to return the value at a given position in a array. We can use excel index function with the MATCH function where match locates and retrieve individual values or entrie row or entire column.

In our example, the INDEX function in excel returns PRICE after selecting the first row and 3rd column.

How to create fiverr account | Fiverr Seller Account | Fiverr Profile Setup

How To Create Fiverr Account | Fiverr Profile Review | Fiverr Course

What is a Virtual Assistant | How to become a Virtual Assistant | Virtua...

What is a Virtual Assistant | How to become a Virtual Assistant | Virtua...

What is Success | Motivational Video | Best Motivational Speech

Web Research | Data Entry | Internet Research Skills

How to Create a Seller Account on Fiverr | How to Create Fiverr Account ...

How To Create Fiverr Account | Fiverr Course | Fiverr Account Create

What is a Virtual Assistant | Virtual Assistant Course - Lesson 2

What is a Virtual Assistant | Virtual Assistant Course

Find and Replace in Excel

Excel's Find and Replace feature is a quick way of finding specific text and we can replace it with other text. We have to go to Special feature to quickly select all the cells with formkulas, comments, conditional formatting, constants and data validation etc. Find in Excel Follow the steps below to find text in Excel.
1. On the Home tab, in the Editing group, click Find & Select.
2. Click Find

3. The 'Find and Replace' dialog box appears.

3. Type the text you want to find. For example and when you find it
Excel selects the first occurrence.
4. Click 'Find Next'.
5. Click 'Find Next' to select the second occurrence.
6. To get a list of all the occurrences, click 'Find All'.
Replace in Excel
To quickly find specific text and replace it with other text, execute the following steps.
On the Home tab, in the Editing group, click Find & Select.

and you can replace the text you selected in find

How to create Excel Dashboard - Excel Dashboards

Excel Dashboards would be covered in our excel tutorial today. We will create Excel Dashboards and learn the following skills in our excel Tutorial. You will learn the following skills in the course. Excel Dashboard Design Excel Dashboard Templates Excel Dashboard for Beginners Excel Dashboard Tutorial
Excel Dashboard Course Excel Dashboard using Pivot Table Excel Dashboard for Data Entry Excel Dashboard for Inventory Managment Excel Dashboard Training How to design excel dashboard Excel Dashboard Examples Interactive Excel Dashboard How to build KPI dashboard in Excel How to build sales dasboard in excel How to create excel dashboard step by step Excel Dashboard in Urdu Excel Dashboard in Hindi how to create excel dsahboard how to create excel dashboard reports how to create excel dashboard free how to create excel impressive excel dashboard how to create excel dashboard how to create excel sales dashboard

Create a Drop-down List in Excel

To create a drop-down list in Excel, execute the following steps.
1. On the second sheet, type the items you want to appear in the drop-down list.

We can also hide the list appeared and it's sheet so that users can not see it
2. On the first sheet select cell a1
3. Select the data tools group in data tab and select data validation

4. On the allow list box click list and select range of the cells 5. Click in the source box and select the range from second sheet

Function Keys in Excel F1 - F3

F1 - Open Excel Help

F2 - Move the insertion point to the en of the conents of the active cells as below selection

F3 - Displays the Paste Name dialog box. For example, select cell E2 below, type =SUM(, press F3 and select a name.

Create a Macro in Excel Basic Settings

Excel VBA is used to automate the tasks by writing macros. We will try to create a simple macro which would be executed on a command button.
We have to first turn on the developer tab
1. Right click on the ribbon and click customize the ribbon from menu.

2. In Customize the Ribbon, select the main tabs.
3. Check the Developer check box.

4. Click Ok 5. Developer Tab is now available It will help you in below Macros in Excel
Macros in Excel for beginners
Macros in Excel 2016
Macros in Excel 2007
Macros in Excel 365
Macros in Excel tutorial

Macros in Excel

Excel Macro is a record and a tool which can record your excel steps and will play it back multiple times for you. VBA Macros save time as they can automate repetitive tasks. It is a piece of programming code that runs in an excel environment and you don't need to be a coder to program macros.
Macros are helpful for beginners and it can work in all the versions of excel.
We have covered
Macros in Excel
Macros in Excel 2016
Macros in Excel for Beginners