Skip to main content

pelatihan.orgLearn. Practice. Grow.

Sign in Sign up

pelatihan.org

Learn web development with a clear roadmap, code practice, and quizzes with instant feedback.

Catalog Sign in Subscription

Excel Curriculum: 9 Chapters, 102 Lessons — pelatihan.org

Home/Catalog/Excel/Curriculum

Curriculum

Excel Curriculum

Learn Excel online with 102 structured lessons, code examples, and 510 quiz questions.

102 lessons 9 chapters 510 questions

Summaries and key points are free to preview. Full lessons, code examples, and quizzes require a subscription.

01

Excel Tutorial

17 lessons

01Excel HOMEThis page is the entrance to Excel material: a series of lessons that takes you from cell and formula introductions to formats, filters, and PivotTables. Excel is a spreadsheet…

02Excel IntroductionExcel is a Microsoft-made spreadsheet application for processing data in the form of rows and columns. An Excel file is called a workbook that contains one or more worksheets,…

03Excel Get StartedThis lesson discusses the first steps in using Excel: open an app, select a blank workbook, save files, and compile your first worksheet. You're also introduced to the Ribbon as…

04Excel OverviewThis lesson gives you a tour of the entire interface of Excel: Name Box, Formula Bar, Ribbon, Sheet tab, and Status Bar. By recognizing each area, you know where to look at the…

05Excel SyntaxThis lesson explains the syntax of the Excel formula: all the formulae start with the symbol =, continue with the name of the function, then the argument in the curve. The…

06Excel RangesRange is a set of cells written with a colon, for example A1:B10, and is the raw material for almost all Excel functions. You can select the range with the mouse, type it…

07Excel FillFill is the ability of Excel to copy the contents or continue the pattern of an array by simply dragging the fill handle. Excel recognizes patterns like 1, 2, 3 or Monday,…

08Excel Move CellsThis lesson discusses moving the contents of the cell: using cut and paste, or dragging the selection edge directly to the target. When the cell containing the formula is moved,…

09Excel Add CellsThis lesson teaches you to insert new cells, rows, and columns into existing data. You choose whether the surrounding cells are moved to the right or down, and Excel…

10Excel Delete CellsThis lesson distinguishes between two frequently exchanged commands: Delete that deletes cells and shifts around cells, versus Clear that simply deletes content, formats, or both…

11Excel Undo RedoUndo cancels the last step, while Redo repeats it again if you change your mind. Excel keeps the history up to about the last 100 steps so the big mistake can still be recovered.…

12Excel FormulasThe formula is the heart of Excel: an expression that calculates the values of numbers, cells, and operators. Excel follows the order of mathematical operations, so…

13Excel Relative ReferenceRelative reference is the cell address without the $ sign that changes following the position when the formula is copied. For example, the =A1 in B1 will be =A2 when copied to…

14Excel Absolute ReferenceThe absolute reference locks the column, row, or both with the $ sign, so the address does not change when the formula is copied. You can add $ manually or press F4 repeatedly to…

15Excel Arithmetic OperatorsExcel supports the standard arithmetic operators: + for addition, - for subtraction, * for multiplication, / for division, and ^ for rank. The percent symbol works directly as a…

16Excel ParenthesesThe parentheses set the order of the calculations in the formula, just like basic mathematics. The contents of the curve are calculated first, and then the result is used for the…

17Excel FunctionsFunctions are ready-made formulas that speed up calculations, for example SUM to sum and AVERAGE to average. The structure is always a function name followed by an argument…

02

Excel Data Analysis

11 lessons

01Excel SortSort rearranges the data line based on the value of a column, from small to large (A to Z) or vice versa. Custom Sort allows sequencing with multiple criteria at once and even…02Excel FilterThe filter hides the unqualified lines so you focus on the data you're looking for. Once AutoFilter is activated, an arrow appears on the column header that you can use to select…03Excel TablesExcel Table converts a regular range into a table object with a header, automatic filter, and mapping format. The formula uses structured references like Table1[Price] to make it…04Excel Conditional FormatConditional Formatting automatically changes the appearance of the cell based on certain conditions, for example marking a value below 60 in red. Because the format follows the…05Excel Highlight Cell RulesHighlight Cell Rules are a category of Conditional Formatting that compares the value of a cell to a specific metric, such as Greater Than, Between, Equal To, or Text that…06Excel Top Bottom RulesTop/Bottom Rules mark cells based on their position in a data set, not a fixed benchmark value. You can highlight top 10 items, top 10%, bottom 10 items, or above average scores.…07Excel Data BarsThe data bar adds a colored bar inside the cell so that the size of the value is visible at a glance without reading the numbers. Available in the gradient fill and solid fill…08Excel Color ScalesColor Scales give the cell's background color based on value, usually from red (low) to green (high) or vice versa. The two- or three-color scale makes the data patterns look…09Excel Icon SetsSets icon adds a small icon such as an arrow, traffic light, or a flashlight inside the cell according to the value. Each icon represents a part of the data distribution, for…10Excel Manage Rules (CF)Conditional Formatting Rules Manager displays all the rules in the range along with the order of priority. You can add, change, delete, or move the rule position, as well as…11Excel ChartsChart transforms tabular data into visual ones like columns, lines, pie, and bar charts that are easier to understand. Excel suggests the appropriate type of chart via…

03

Excel Formatting

8 lessons

01Excel FormattingFormatting changes the data's appearance to be easy to read without changing the value stored within the cell. The basic command group is in the Home tab: font, alignment, and…02Excel Format PainterPainter format copies the appearance of a cell such as font, color, and border to another cell without touching the contents. One click is used for a single use, while a double…03Excel Format ColorsThis lesson discusses cell coloring: Fill Color for the background and Font Color for the letter color. Theme Colors are taken from the workbook theme so they change as the theme…04Excel Format FontsThe Font group sets the type, size, thickness, slope, bottom line, and color of the text. The Grow Font and Shrink Font buttons change size per step without typing numbers,…05Excel Format BordersThe worksheet's built-in line is just a screen view so it often doesn't go with print, and that's where the Borders format is needed. You can choose the outer line, the inner…06Excel Format NumbersThe numeric format determines how values are displayed: Numbers with decimals, Currency with money symbols, Percentage, Date, and so on. The important thing is, the format just…07Excel Format GridsGridlines are thin lines of cell division that appear as guides on the screen. You can display or hide it through the View tab, and enable the print option to join the print in…08Excel Format SettingsThe format setting includes printed views: Portrait or Landscape orientation, paper size, margins, and printed area. Print Area makes only certain sections of the sheet printed,…

04

Excel PivotTables

7 lessons

01Excel PivotTable IntroPivotTables is a data summary tool that converts long lists into interactive reports just by dragging fields. You can see the total, the average, the sum, and the recap per…02Excel Create PivotTableCreating a PivotTable starts by selecting the source data, then Insert > PivotTable, then determining the result location: new sheet or existing sheet. After that, it's just…03Excel PivotTable ValuesThe Values field contains summarized numbers, and Excel automatically selects aggregates such as Sum or Count according to the data type. You can change it by using Value Field…04Excel PivotTable FilterFilters on the PivotTable limit the data that is summarized without touching the source sheet, for example displaying only one region or a specific date range. You can use the…05Excel PivotTable GroupGroups on the PivotTable group raw data into more readable categories, such as dates into months or quarters, and numbers into ranges such as 0-50, 51-100. The grouping is done…06Excel Calculated FieldCalculated Fields add virtual columns to the PivotTable that are calculated from other fields with a formula, for example, Profit = Revenue - Cost. This new field does not change…07Excel PivotChartA pivot chart is a chart that is directly connected to a pivot table, so that the filter on the pivot is drawn on the chart and vice versa. You can choose a chart type like a…

05

Excel Case

2 lessons

01Case: Poke MartThe Poke Mart case study combines sales of fictional products into one real exercise: compose the data, ensure the column is neat, and then calculate total and summary sales. You…02Case: Poke Mart, StylingThe further part of the Poke Mart case study focused on the display: giving the header color, edge lines, format of money numbers, and the correct width of columns. The visual…

06

Excel Functions

50 lessons

01ANDThe AND function returns TRUE only if the given entire condition is TRUE. You can compare multiple cells at once, like a value above 60 and a full presence. AND is often combined…02AVERAGEAVERAGE calculates the arithmetic mean of a set of numbers, that is, the total divided by the number of cells containing the numbers. Empty cells and text are passed so the…03AVERAGEIFAVERAGE calculates the average only for cells that meet a certain requirement, for example, the average grade A value only. The structure is range, criteria, then optional…04AVERAGEIFSAVERAGEIFS is an advanced version that supports several criteria at once, for example the average sales of a particular product in a particular region. Unlike AVERAGEIF, the…05CONCATCONCAT combines several text, numbers, or cell references into a new string. The modern version of CONCAT accepts ranges at once, as opposed to the old CONCATENATE which only per…06COUNTCOUNT counts the number of cells that contain numbers in the range, so text and empty cells are not counted. The COUNTA version counts all the non-empty cells, and the COUNTIF…07COUNTACOUNTA is an Excel statistical function that calculates the number of cells in an empty range, including those containing text, numbers, zeros, or error values. Unlike COUNT,…08COUNTBLANKCOUNTBLANK is an Excel function that calculates the number of empty cells in a range. A cell is considered empty if it contains nothing or contains a formula that produces empty…09COUNTIFCOUNTIF is an Excel function that calculates the number of cells that meet a certain criterion in a range. The criteria can be numbers, text, or logical expressions such as…10COUNTIFSCOUNTIFS is an Excel function that calculates the number of cells that meet multiple criteria at once. Each pair of ranges and criteria is given in sequence, and all criteria…11DATEDIFDATEDIF is an Excel hidden function for calculating the difference between two dates in a given unit. The unit parameters are "Y" (year), "M" (month), or "D" (day), as well as…12FILTERA FILTER is a dynamic Excel function that displays some of the rows of a range based on a certain criterion and the result spreads (spill) to several cells. The syntax is. This…13HLOOKUPHLOOKUP is an Excel search function that searches for values on the first row of a horizontal table and returns values from a particular row below it. The syntax is. This…14IFIF is an Excel basic logic function that returns the first value if the condition is correct and the second value if it is wrong. The syntax is. This function forms the basis for…15IFERRORIFERROR is an Excel function that handles error values by displaying a more friendly replacement value. The syntax is. If the first value is not an error, the result is continued…16IFSIFS is an Excel logic function that checks many consecutive conditions and returns the result for the first condition that is worth TRUE. The syntax is. This function replaces…17INDEXINDEX returns the value of an array based on the number of rows and columns given. The syntax is. This function is very flexible because it can stand alone or be combined with…18INDEX MATCHThe combination of INDEX and MATCH replaced VLOOKUP for a more flexible and resilient search. MATCH determines the position of the key value, and then the INDEX takes the result…19LEFTLEFT is an Excel text function that takes a number of characters from the left side of a text. The syntax is LEFT(text, [num_chars]); if num_chars are passed, one character is…20LENLEN is an Excel text function that calculates the number of characters in a text, including spaces. The syntax is. This function is often used for validating input length, such…21LOWERLOWER is an Excel text function that converts all the letters in the text into lowercase letters. The syntax is. This function does not change numbers, symbols, or spaces so it…22MATCHMATCH is an Excel function that returns the position of a value in a range. The syntax is. The match_type argument is 0 for the exact search, 1 for the smallest largest value,…23MAXMAX is an Excel statistical function that returns the largest value of a set of numbers or ranges. The syntax is. Text, logic, and empty cells are ignored so that the result is…24MEDIANMEDIAN is an Excel statistical function that returns the mean of a set of numbers after the data is sorted. If the data is even, the result is the average of the two middle…25MIDMID is an Excel text function that takes a number of characters from the middle of a text. The syntax is three-word. This function is useful for extracting code or specific parts…26MINMIN is an Excel statistical function that returns the smallest value of a set of numbers or ranges. The syntax is. Text, logic, and empty cells are ignored so that the result is…27MODEMODE is an Excel statistical function that returns the most frequently occurring value (mode) in a range. The modern version is MODE.SNGL for one mode and MODE.NG for several…28NETWORKDAYSNETWORKDAYS is an Excel function that calculates the number of working days between two dates, which are Monday through Friday, excluding weekends. The syntax is. Also available…29NPVNPV (Net Present Value) is an Excel financial function that calculates the present value of a series of future cash flows at a certain discount rate. The syntax is. A positive…30OROR is an Excel logic function that returns TRUE if at least one of the tested conditions is true. The syntax is. This function is often combined with IF to create an alternative…31PROPERPROPER is an Excel text function that converts the first letter of each word into capital and the rest into lowercase. The syntax is. This function is very helpful in…32RANDRAND is an Excel random function that produces a decimal number between 0 (inclusive) and 1 (exclusive). This function is volatile, which means the result changes every time the…33RIGHTRIGHT is an Excel text function that takes a number of characters from the right side of a text. The syntax is RIGHT(text, [num_chars]); if num_chars are passed, one last…34ROUNDROUND is an Excel numerical function that rounds a number to a certain decimal number. The syntax is ROUND(number, num_digits), where num_digits are positive for decimal and…35SORTSORT is an Excel dynamic array function that sorts the contents of a range and then displays the result in multiple cells (spill). The syntax is. This function is available in…36STDEV.PSTDEV.P is an Excel statistical function that calculates standard deviation for the entire population of data. The syntax is STDEV.P(number1, [number2],...). Use this function…37STDEV.SSTDEV.S is an Excel statistical function that calculates the standard deviation based on a sample of data. The syntax is STDEV.S(number1, [number2],...). This function divides by…38SUBSTITUTESUBSTITUTE is an Excel text function that replaces the appearance of a particular text in a string with new text. The syntax is. Unlike REPLACE which works by position,…39SUMSUM is an arithmetic function in Excel that sums the whole number in a given range or argument. The syntax is. This function is most commonly used to calculate the total data on…40SUMIFSUMIF is an Excel function that sums cells in a range based on one particular criterion. The syntax is. If the sum_range argument is passed, the first range becomes the basis of…41SUMIFSSUMIFS is an Excel function that sums values based on several criteria at once. Unlike SUMIF, the sum_range on SUMIFS is written as the first argument. All the criteria must be…42SUMPRODUCTSUMPRODUCT multiplies the elements in a position of one or more arrays, and then sums the result. This function is often used to compute the conditional total and double count…43TEXTJOINTEXTJOIN is an Excel text function that combines many values with a particular delimiter. The syntax is. The ignore_empty argument determines whether a blank cell is passed so…44TODAYTODAY is an Excel date function that returns the date today in the form of a serial value. This function is volatile so the result is updated every time the worksheet is…45TRIMTRIM is an Excel text function that removes excess space at the beginning and end of the text, while converting the middle space array into one space. The syntax is. This…46UNIQUEUNIQUE is an Excel dynamic array function that returns a list of unique values from a range. The syntax is. This function displays the temporary result through a formula without…47UPPERUPPER is an Excel text function that converts all the letters in the text into capital letters. The syntax is. Numbers, symbols, and spaces are unaffected, so this function is…48VLOOKUPVLOOKUP is an Excel search function that looks for values vertically on the first column of a table and returns values from other columns on the same row. The syntax is. This…49XLOOKUPXLOOKUP is a new generation search function that replaces VLOOKUP, HLOOKUP, and INDEX-MATCH. The syntax is. This function can search in any direction and have an in-built message…50XORXOR (exclusive OR) is an Excel logic function that returns TRUE if the number of correct value conditions is odd. Unlike the OR that only needs one condition correct, the XOR…

07

Excel How To

4 lessons

01Convert Time to SecondsConverting time into seconds is useful when calculating the duration of work or activity. The key is that Excel stores time as a fraction of a day, so the time value can be…02Difference Between TimesThe time difference between two is calculated by subtracting one time value from the other value. The result is a fraction of a day that can be formatted as a clock. When the…03NPV (Net Present Value)Calculating Net Present Value (NPV) allows us to judge whether an investment is worthwhile based on the value of money today. So, you put the cash flow per period and the…04Remove DuplicatesThe Remove Duplicates menu removes the double row from the table based on the selected column. This feature is useful for cleaning up transaction data, contact lists, or survey…

08

Excel Examples

3 lessons

01Excel SyllabusThe Excel syllabus is a structured list of learning topics that explains the order and coverage of the material to be mastered. From the interface basics to the functionality and…02Excel Study PlanThe Excel study plan is a learning plan that divides material into weekly targets with regular practice. A gradual approach makes concepts easier to stick to and reduces the risk…03Excel TrainingExcel training is a structured training session to improve data processing skills, from basic formulas to interactive reports. The ideal training material is project-based so…

All-course subscription

$5.00 / 30 days

Access every course, lesson, and quiz. Progress is saved automatically.

This curriculum is free to browse. Activate a subscription to unlock full lessons, code examples, and quizzes.

Activate subscription Back to course