Time-saving ways to insert formulas into Excel
Written byCFI Team
Updated October 31, 2022
Mastering the basic Excel formulas is critical for beginners to become highly proficient infinancial analysis.Microsoft Excelis considered the industry standard piece of software in data analysis. Microsoft’s spreadsheet program also happens to be one of the most preferred software byinvestment bankersand financial analysts in data processing,financial modeling, and presentation.
This guide will provide an overview and list of some basic Excel functions.
Once you’ve mastered this list, move on to CFI’s advanced Excel formulas guide!
Basic Terms in Excel
There are two basic ways to perform calculations in Excel: Formulas and Functions.
In Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3.
Functions are predefined formulas in Excel. They eliminate laborious manual entry of formulas while giving them human-friendly names. For example: =SUM(A1:A3). The function sums all the values from A1 to A3.
- Excel is still the industry benchmark for financial analysis and modeling across almost all corporate finance functions. This course is designed to highlight some of the most important basic Excel formulas.
- Mastering these will help a learner build confidence in Excel and move on to more difficult functions and formulas.
- There are also several different ways to enter a function in Excel, as shown below.
Five Time-saving Ways to Insert Data into Excel
When analyzing data, there are five common ways of inserting basic Excel formulas. Each strategy comes with its own advantages. Therefore, before diving further into the main formulas, we’ll clarify those methods, so you can create your preferred workflow earlier on.
1. Simple insertion: Typing a formula inside the cell
Typing a formula in a cell or the formula bar is the most straightforward method of inserting basic Excel formulas. The process usually starts by typing an equal sign, followed by the name of an Excel function.
Excel is quite intelligent in that when you start typing the name of the function, apop-upfunction hint will show (see below). It’s from this list you’ll select your preference. However, don’t press the Enter key after making your selection. Instead, press the Tab key and Excel will automatically fill in the function name.
2. Using Insert Function Option from Formulas Tab
If you want full control of your function’s insertion, using the Excel Insert Function dialogue box is all you ever need. To achieve this, go to the Formulas tab and select the first menu labeled Insert Function. The dialogue box will contain all the functions you need to complete yourfinancial analysis.
3. Selecting a Formula from One of the Groups in Formula Tab
This option is for those who want to delve into their favorite functions quickly. To find this menu, navigate to the Formulas tab and select your preferred group. Click to show a sub-menu filled with a list of functions.
From there, you can select your preference. However, if you find your preferred group is not on the tab, click on the More Functions option – it’s probably just hidden there.
4. Using AutoSum Option
For quick and everyday tasks, the AutoSum functionis your go-to option. Navigate to theFormulas tab and click the AutoSum option. Then click the caret to show other hidden formulas. This option is also available in the Home tab.
5. Quick Insert: Use Recently Used Tabs
If you find re-typing your most recent formula a monotonous task, then use the Recently Used selection. It’s on the Formulas tab, a third menu option just next to AutoSum.
Free Excel Formulas YouTube Tutorial
Watch CFI’s FREE YouTube video tutorial to quickly learn the most important Excel formulas. By watching the video demonstration you’ll quickly learn the most important formulas and functions.
Seven Basic Excel Formulas For Your Workflow
Since you’re now able to insert your preferred formulas and function correctly, let’s check some fundamental Excel functions to get you started.
The SUM function is the first must-know formula in Excel. It usually aggregates values from a selection of columns or rows from your selected range.
=SUM(number1, [number2], …)
=SUM(B2:G2) – A simple selection that sums the values of a row.
=SUM(A2:A8) – A simple selection that sums the values of a column.
=SUM(A2:A7, A9, A12:A15) – A sophisticated collection that sums values from range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally adds from A12 to A15.
=SUM(A2:A8)/20 – Shows you can also turn your function into a formula.
The AVERAGE function should remind you of simple averages of data, such as the average number of shareholders in a given shareholding pool.
=AVERAGE(number1, [number2], …)
=AVERAGE(B2:B11) – Shows a simple average, also similar to (SUM(B2:B11)/10)
The COUNT function counts all cells in a given range that contain only numeric values.
=COUNT(value1, [value2], …)
COUNT(A:A) – Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows.
COUNT(A1:C1) – Now it can count rows.
Like the COUNT function, COUNTA counts all cells in a given rage. However, it counts all cells regardless of type. That is, unlike COUNT that only counts numerics, it also counts dates, times, strings, logical values, errors, empty string, or text.
=COUNTA(value1, [value2], …)
COUNTA(C2:C13) – Counts rows 2 to 13 in column C regardless of type. However, like COUNT, you can’t use the same formula to count rows. You must make an adjustment to the selection inside the brackets – for example, COUNTA(C2:H2)will count columns C to H
The IF function is often used when you want to sort your data according to a given logic. The best part of the IF formula is that you can embed formulas and functions in it.
=IF(logical_test, [value_if_true], [value_if_false])
=IF(C2<D3,“TRUE”,”FALSE”) – Checks if the value at C3 is less than the value at D3. If the logic is true, let the cell value be TRUE, otherwise, FALSE
=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – An example of a complex IF statement. First, it sums C1 to C10 and D1 to D10, then it compares the sum. If the sum of C1 to C10 is greater than the sum of D1 to D10, then it makes the value of a cell equal to the sum of C1 to C10.
TheTRIM function makes sure your functions do not return errors due to extra spaces in your data. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding duplicated data to your spreadsheet.
TRIM(A2) – Removes empty spaces in the value in cell A2.
7. MAX & MIN
The MAX and MIN functions help in finding the maximum number and the minimum number in a range of values.
=MIN(number1, [number2], …)
=MIN(B2:C11) – Finds the minimum number between column B from B2 and column C from C2 to row 11 in both columns B and C.
=MAX(number1, [number2], …)
=MAX(B2:C11) – Similarly, it finds the maximum number between column B from B2 and column C from C2 to row 11 in both columns B and C.
Thank you for reading CFI’s guide to basic Excel formulas. To continue your development as a world-class financial analyst, these additional CFI resources will be helpful:
- Free Excel Fundamentals Course
- Advanced Excel Formulas
- Benefits of Excel Shortcuts
- List of Excel Functions
- Valuation Modeling Excel
- Data recording. Basic Excel users must know how to navigate and record data. ...
- Formulas. There are some formulas you need to know for basic Excel skills: ...
- Charts and graphs. ...
- Data organization. ...
- Pivot tables.
Excel can be a challenging tool to master if you're a beginner and have no experience in data or spreadsheets. However, learning the basics is a straightforward process that only takes a short amount of time, especially if you get some help from online courses.What is the most useful formula in Excel? ›
Sum. The SUM function in Excel is among the most commonly used formulas. This formula adds the values of the cells within it.What is the fastest way to improve Excel skills? ›
- Master keyboard shortcuts. Excel comes with a slew of keyboard shortcuts to help you work faster and more productively. ...
- Conditional formatting. ...
- Macros. ...
- PivotTables. ...
- Charts. ...
- Filters/ AutoFilter. ...
- COUNTIF. ...
- Flash Fill.
Although it's possible to gain a basic understanding of Excel's interface and core functions in just a few hours, it can require additional time and study to master its more complex capabilities. It takes most Excel users approximately 18-20 hours to fully learn this spreadsheet application.How can I practice Excel at home? ›
- Top 10 formulas and functions in Excel.
- Free Excel Online Exercises.
- Excel Basics – Zero to Hero.
- Vlookup – Tutorial with Example and Exercise Sheet.
- Pivot Tables Tutorial.
- Excel Shortcuts – Windows and Mac.
It is possible to learn Excel in a single day. If you dedicate a full day to learning Excel you will be able to gain a firm understanding of the basic functionality of how to work with spreadsheets and perform basic tasks.Can I learn basic Excel in a day? ›
It's impossible to learn Excel in a day or a week, but if you set your mind to understanding individual processes one by one, you'll soon find that you have a working knowledge of the software. Make your way through these techniques, and it won't be long before you're comfortable with the fundamentals of Excel.Can I learn Excel in a month? ›
Learning Excel does not need to take weeks, months, or years. In fact, you can learn many of the core functions of Excel in a single day. That is – if you learn from a good Excel course with a knowledgeable teacher.Can I teach myself Excel? ›
Microsoft offers free Excel classes online on edX to learn both fundamental and advanced Excel skills. Introduction to Data Analysis using Excel is a 4-week, self-paced online course perfect for learners with little or no prior experience using the software.
- Data Entry and Storage. At its most basic level, Excel is an excellent tool for both data entry and storage. ...
- Collection and Verification of Business Data. ...
- Administrative and Managerial Duties. ...
- Accounting and Budgeting. ...
- Data Analysis. ...
- Reporting + Visualizations. ...
The main uses of Excel include: Data entry. Data management. Accounting.How can I learn formulas quickly? ›
Practice, practice and practice: Practice makes you perfect! When you practice using the formulas you want to learn, your brain understands the application of the formula and remembers it. Try solving and practicing problems using the formula and you will see results! Repetition leads to memorization.How can I learn formulas faster? ›
- Grow your interest in the concept in which you are studying. ...
- Connect your concept with a visual memory. ...
- Knowing the process behind maths formulas. ...
- Always solve the problems with math formulas. ...
- Write down maths formulas. ...
- Stick your written formulas on your wall.
- #1 – VLOOKUP Formula in Excel.
- #2 – INDEX Formula in Excel.
- #3 – MATCH Formula in Excel.
- #4 – IF AND Formula in Excel.
- #5 – IF OR Formula in Excel.
- #6 – SUMIF Formula in Excel.
- #7 – CONCATENATE Formula in Excel.
- #8 – LEFT, MID, and RIGHT Formula in Excel.
- (a+b)2 = a2 + b2 + 2ab.
- (a-b)2 = a2 + b2 – 2ab.
- (a+b) (a-b) = a2 – b. ...
- (x + a)(x + b) = x2 + (a + b)x + ab.
- (x + a)(x – b) = x2 + (a – b)x – ab.
- (a + b)3 = a3 + b3 + 3ab(a + b)
- (a – b)3 = a3 – b3 – 3ab(a – b)
- (x – a)(x + b) = x2 + (b – a)x – ab.
If you practice every day and dedicate around 2-3 hours every day to learn the concepts, then you can learn it within four weeks. But, to master the concepts in Excel, you need to use the tricks and formulas on a daily basis. You need about 8-10 days to finish the Basic and Advance excel course.Can I learn Excel in 2 days? ›
Excel can be easily learned within 15–30 days. But to master it, there is no time limit.Is learning Excel worth it in 2022? ›
Is Excel worth learning in 2022? Definitely! Many business, accounting, finance, and similar majors learn to use Microsoft Excel as part of their curriculum, but most degree programs do not. Those who have been in the workforce for a long time may never have had the chance to improve their Excel skills.What Excel skills are employers looking for? ›
- Organise and Clean your Data in Excel.
- Present your Excel Data with Interactive Graphs and Charts.
- Look Up Values using VLOOKUP, HLOOKUP, and XLOOKUP.
- Protect Your Cells and Worksheets.
- Save Time with Basic Excel Macros.
- Frequently Asked Questions.
- Final Thoughts.
1,50,000 - 3,00,000 PA.Is SQL better than Excel? ›
Unlike Excel, SQL can handle well over one million fields of data with ease. SQL queries are also more flexible and powerful than Excel formulas. Data analysts use SQL to interact with enormous databases by: Manipulating data.Where is Excel mostly used? ›
Excel is most commonly used in business settings. For example, it is used in business analysis, human resource management, operations management and performance reporting. Excel uses a large collection of cells formatted to organize and manipulate data and solve mathematical functions.What cool things can Excel do? ›
- Attendance tracking project. ...
- Daily expense tracking. ...
- To-do list. ...
- Beautiful artwork creation. ...
- Game development in Excel. ...
- Creating Sudoku puzzles. ...
- Making web games. ...
- Creating a 3D pendulum.
Instead of crossing your eyes over a full spreadsheet, Excel will easily regroup your data so you can make better comparisons. By viewing your data in these different perspectives, you can get a better picture of where your revenue is coming from by customer, offering, month, etc.How do all formulas begin? ›
A formula always starts with an equal sign (=), which can be followed by numbers, math operators (such as a plus or minus sign), and functions, which can really expand the power of a formula.Who owns Excel? ›
Microsoft Excel, spreadsheet application launched in 1985 by the Microsoft Corporation.Why is Excel called Excel? ›
Before Microsoft launched Excel, a software with name Lotus 123 was used as spreadsheet software. I personal learned that in 1997. Microsoft want to show that the software they have developed for spreadsheet is more Excel than Lotus 123. Hence they name it Excel.What are the 10 formulas in Excel? ›
- Overview of formulas in Excel. Article.
- XLOOKUP. Article.
- VLOOKUP. Article.
- SUM function. Article.
- COUNTIF function. Article.
- IF function. Article.
- IFS. Article.
- SUMIF. Article.
- Table formatting. What it does: transforms your data into an interactive database. ...
- Pivot tables. What it does: summarizes data and finds unique values. ...
- Charting. ...
- COUNTIFS. ...
- SUMIFS. ...
- IF Statements. ...
- CONCATENATE. ...
The most common business uses of MS Excel are business analysis, managing lists of people, operations management, and performance reporting. The software is also handy for office administration, project management, and managing programs, contracts and accounts.What are the 4 basic functions in Excel? ›
Excel formulas enable you to perform calculations such as addition, subtraction, multiplication, and division. In addition to these, you can find out averages and calculate percentages in excel for a range of cells, manipulate date and time values, and do a lot more.What is VLOOKUP formula? ›
In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE). Play.How many formulas are in Excel? ›
Learn how to use all 300+ Excel formulas and functions including worksheet functions entered in the formula bar and VBA functions used in Macros.What are the 20 Excel functions? ›
- VLOOKUP. The function is used to look up for a piece of information in a large segment of data and pull that data to your newly formed table. ...
- Sum Function. ...
- MAX MIN function. ...
- IF Function. ...
- SUMIF Function. ...
- COUNTIF Function. ...
- AND Function. ...
- OR function.
- Sort. ...
- Filter. ...
- SUMIF function. ...
- Pivot Tables. ...
- Text Formulas. ...
- IF formulas. ...
- Charts. ...
- Conditional Formatting.