Often confused when going to use Excel formulas? Don't worry, Jaka has made a summary that is easy to understand and can be used as a cheat sheet.
For students or office workers, you must be familiar with Microsoft Excel.
Software The best number processor made by Microsoft is proven to be able to speed up its use when calculating various numbers.
Well, the key to success in using Microsoft Excel is to use its formulas to make calculations easier. Unfortunately, many people don't know anything Excel formula which can be used.
Are you one of them?
Do not worry. ApkVenue has a cheat list of important Excel formulas that you must know.
About Microsoft Excel
Before knowing what formulas are in Microsoft Excel, it's good that you know the sundries gang first about software the.
Microsoft Office Excel or often just called Excel is a program spreadsheet or a worksheet that you can use on a PC or mobile phone.
Part of the larger Microsoft Office family, Excel has features to make calculations and graph creation easier.
History of Microsoft Excel
Before it was named Excel, software this is the first time released under the name Multiplan in 1982. Microsoft did that to compete with software spreadsheet others, especially Lotus 1-2-3.
Because it failed to compete, Microsoft finally changed its name to Excel in 1985 to Macintoshits Apple. For version Windowsit was only released two years later, namely 1987.
Since then, Excel began to displace Lotus 1-2-3's position as spreadsheet software most popular for processing data.
Microsoft Excel Functions
On Microsoft Excel, we will work in one workbook. Inside one workbook, there are several worksheet which you can use to separate different data, so you don't have to create workbook the new one.
Excel is an application that is intended to process data automatically, both data in the form of basic calculations (add, subtract, divide, times) or use more complex functions.
In addition to processing data, Excel can also be used to complete various administrative purposes because of its ability to accommodate large amounts of data.
With its ability to quickly find patterns and processes in data, Excel is perfect for creating:
Make company financial reports, such as company profits and losses, employee salaries, and so on.
Create a attendance list for an event, although you can also use Microsoft Word.
Create a list of grades for teachers and lecturers because Excel is able to perform features such as finding the average value, finding the middle value, finding the minimum maximum value, and so on.
Calculate data quickly, not only basic calculations but also variations.
Create graphs and tables for a data table. For example, the growth chart traffic a website, economic growth charts, and so on.
Presenting data precisely, neatly, and accurately
Advantages of Microsoft Excel
One of the things that makes Microsoft Excel popular is its friendly interface and quite complete features.
Same as spreadsheet software On the other hand, Excel has cells that are made up of rows and columns. The data that can be accommodated by Excel are 1 million lines and 16 thousand columns.
With its ability to accommodate this massive data, Excel will make it easier for us to process the data we have to analyze and represent it in the form of graphs or charts. chart.
Other than that, software it has complete formulas and formulas. Moreover, now you can access the Excel application via smartphone that you have.
Disadvantages of Microsoft Excel
Behind all these advantages, of course Excel has some drawbacks. One of which is software This will take up quite a lot of RAM when it is operated.
In addition, the formulas that are too complete make new users take a long time to understand the functions of these formulas.
Excel's auto-calculation function can also cause you to experience errors if you enter the wrong formula.
Microsoft Excel File Types
Maybe you don't know, gang, actually Excel has several different types! So that you don't get confused about what types of Excel files are, let's just read the article below!
.xls format
This format is a format default from Excel before version 12 (Excel 2003 and below)
.xlsx format
Format default for versions of Excel 2003 and above that have been based on XML (Extensible Markup Language). The existence of XML will make it easier to exchange data between various systems.
.xlt Format format
Format for worksheet templates Excel for versions 12 and below. This one format is still based on BIFF (Binary Interchange File Format) and able to save VBA macros (Visual Basic for Applications).
Excel Format More . . .
.XML Format
Also known as XML Spreadsheet, this format cannot accommodate VBA macros.
.xla . format
Is a format default for Excel Add-ins before Excel 2003. An add-in is an add-in that can provide optional commands and features for Microsoft Excel.
.xlsm . format
Is a format worksheet for Microsoft Excel 2003 and above. This format is XML-based and is capable of storing data VBA macros.
.xlsb Format format
Format worksheet which is based on XML but is encoded in the form of binary numbers. The advantage of this format is the speed of data processing, considering that this type of Excel is intended to process data worksheet Very large.
.xltm Format format
Is a substitute for format .xlt capable of storing VBA macros and has an XML base.
.xlam Format format
Is the format for Excel Add-ins which default able to handle VBA macros.
Download Microsoft Excel Application
Well, after knowing a few facts about Microsoft Excel, it's time to find out Excel formula What are the things that are often used in the world of work.
Previously, do you already have Microsoft Excel application? If not, first download the application below!
Microsoft Corporation Office & Business Tools Apps DOWNLOAD If you are looking for an application for Android or IOS, ApkVenue has also prepared the download link. Just click on the link in the table below!
| Information | Microsoft Excel |
|---|---|
| Developer | Microsoft Corporation |
| Reviews (Number of Reviewers) | 4.5 (1.520.751) |
| Size | Varies by device |
| Install | 500.000.000+ |
| Android Minimum | Varies by device |
| Download | Android
|
Excel Formulas in the World of Work that are Often Used
Already have the app? If so, this time Jaka will share complete set of excel formulas and their functions. To be fast, you can use Ctrl + F. Here's the list!
IF
Function Excel formula IF is to take one of two values based on a condition. The logic is the same as IF in programming languages. In one function, you can put multiple IFs depending on your needs.
IF Rumus formula
IF(logical_test,[value_if_true],[value_if_false])Example IF
=IF(2<3,"True","False") > "True" =IF(2>3,"True","False") > "False"
SUM
Function Excel SUM . formula is the sum of a set of numbers. What you can add can be numbers or column to column.
For example, you want to add up data from columns A1 to A25, then you just use this formula by selecting columns A1 to A25.
SUM formula
Sum(number1,[number2],..]Example SUM
=sum(2,3,5,6,11) > 28 =sum(A1:A25)
Count
Function Excel Count rumus formula almost the same as the Sum function, which calculates the number of cell containing numbers.
Count formula
count(value1,[value2],..]Example Count
=count(a1:a10)
CountA
Function Excel CountA formula is to calculate the amount cell for which there is data.
CountA formula
counta(value1,[value2],..]Example CountA
=count(a1:a10)
CountIF
Function Excel CountIF formula is to calculate the amount cell on a range which has one particular criterion.
CountIF Rumus formula
countif(range, criteria)CountIF Example
=countif(a1:a10,"A")
Excel Formulas More . . .
SumIF
Function Excel SumIF formula is the sum of a items in the table that matches certain conditions.
SumIF Rumus formula
sumif(range, criteria,[sum_range])SumIF example
=sumif(a1:a6,"Tempe",B1:B6)
Match
Function Excel Match rumus formula is to show the relative position of a items in the table that matches certain conditions or criteria.
For example, we want to find whether in a table there is a person's name certain, you can use this one function.
Match formula
match(lookup_value,lookup_array,[match_type])Example Match
=match("Jojon",A1:A20,0)
VLookUp
Function Excel VLookUp formula is looking for a items in a column, in a table, and retrieve values that are in another column in that table.
For example, we want find out when birthdays or the number of children of certain employees. The data to be displayed must be taken from a table arranged in a vertical format.
VLookUp formula
vlokup(lookup_value,lookup_array,[match_type])VLookUp Example
=vlookup("Jojon",A1:C11,3,0)
HLookUp
Together with VlookUp, only on the formula HlookUP The data taken must be horizontal.
HLookUp formula
=HLOOKUP(lookup_value,lookup_array,[match_type])HLookUp Example
=HLOOKUP("Jojon",A1:C11,3,0)
SumIf
Function Excel SumIfs rumus formula is looking for a items in a column, in a table, and retrieve values that are in another column in that table.
For example, we want find out when birthdays or the number of children of certain employees.
SumIfs . formula
sumifs(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)SumIfs Contoh Example
=sumifs(d1:d10,a1:a10,'Jojon",E1:E10,"February")
Round
Function Excel Round formula is to round the number to the decimal number you want.
Round formula
round(number,num_digit)Example Round
=round(12:3456789,2)
Trim
Function Excel Trim rumus formula is to justify the text by removing too many spaces. This formula is suitable for tidying up a data table.
Trim formula
trim(text)Trim Example
=trim("This is an easy to learn excel formula ")
Clean
Almost similar to Trim, it's just a formula Clean used to remove non-printable characters (non-printable characters).
Clean Formula
= CLEAN(cell to be cleaned))Example CLEAN
=CLEAN(B2)
proper
The function of Excel Proper formulas is to convert text to shapes proper case (first letter is capitalized).
Proper Formula
proper(text)Proper Example
=proper("This is an easy to learn eXcel formula") > This is an easy to learn Excel formula
Upper
Function Excel Upper rumus formula is to convert text to shape upper case aka all capital letters.
Upper Rumus formula
upper(text)Upper Example
=upper("this is a simple excel formula at work") > THIS IS A SIMPLE EXCEL FORMULA FOR WORK
Lower
Function Excel Lower formula is to convert text to shape lower case (all lowercase).
Lower formula
lower(text)Lower Example
=lower("Don't forget to download the JalanTikus app") > don't forget to download the JalanTikus app
Average
Function Excel Average formula you can use to find the average value of a data.
Average formula
=AVERAGE(number1:number2)Average Example
=AVERAGE(L3:L6)
Max
If you want to find the highest value in a row of numbers, you can use the formula Max.
Max formula
=MAX(number1:number2)Max Example
=MAX(L3:L6)
Min
On the other hand, if you want to find the lowest value in a row of numbers, you can use the formula Min.
Min formula
=MIN(number1:number2)Example Min
=MIN(L3:L6)
Concatenate
When you want to combine multiple values on cell with some word modifications, you can use the formula Concatenate.
Concatenate Formula
=CONCATENATE(text1;text2;...)Example of Concatenate
= CONCATENATE( Reader ;K6; Sum ;L6)
Left
If you need to take multiple characters in a word or number, use the formula Left. For example in the word JALANTIKUS. you can just take the word JALAN.
Left . formula
=LEFT(cell to retrieve; number of characters to display)Left . Example
=LEFT(B25;5)
Right
Same as formula Left, it's just a formula Right pick up the character from the right.
Right formula
=RIGHT(cell to retrieve; number of characters to display)Example Right
=RIGHT(B25;5)
Mid
Same as formula Left and Right, it's just a formula Mid take the character from the middle.
Mid formula
=MID(cell to retrieve; number of characters to display)Example MID
=MID(B25;5)
Rep
If you want to count the number of votes in the student council president election, you can use the formula Rept. For example, one sound you symbolize by Turus ( | ), then you can count how many Turus there are in the table.
Rept formula
=REPT( | ;how many repetitions)Example Rept
=REPT( | ;(B25/$B$30)*100)
Date
If you want to add a date to your Excel file, you can use a formula Date.
Date formula
=DATE(year;month;day)Example Date
=DATE(2019;2;1)
year
Formula year used when you want to retrieve year data on a cell date format.
Year formula
=YEAR(cell you want to retrieve data)Sample Year
=YEAR(B3)
Month
Formula Month used when you want to retrieve month data on a cell date format.
Month formula
=MONTH(cell you want to retrieve data)Month Example
=MONTH(B3)
Day
Formula Day used when you want to retrieve day data on a cell date format.
Day formula
=DAY(cell you want to retrieve data)Example DAY
=DAY(B3)
That's variety Excel formula in the world of work that is most often used. If you know other excel formulas, don't forget share in the comments!
