This comprehensive tutorial video covers Excel
Basic in four main chapters. The First chapter, Raw Data, involves Data
Entry, Top Formula part 1, Formatting & Table Second chapter, Validation, involves Filter,
Sort & Find, Top Formula part 2, Go To Special, as well as Common Errors Handling. In third chapter, Analysis, we will learn
Conditional Formatting, Top Formula part 3, Chart Basic & Pivot Table part 1. And the fourth chapter is Report where we
will learn Pivot Table part 2, Top Formula pa
rt 4 & how to create report on a Simple
Template. Before delving into the main chapters, let
me introduce you to Excel. For those who are new to Excel, accessing
the program is simple. Click on the Windows logo located on the Task
Bar or on the keyboard, and then locate the Excel icon, which is typically
available in the Pinned section by default. If the Excel icon is not visible, click on
All Apps, search for Excel, right click the mouse, and select Pin to Start. This action ensures that the Ex
cel icon remains
in the Pinned section for future access. Once located, click on the Excel icon to launch
the program. From here, you can click on blank workbook
to initiate the creation of a new Excel file, which will open automatically. By default, this new file will be named Book1
and is classified as a workbook. Workbooks are where data or reports are created
using Excel. In the next chapter, I will explain how to
change the default name of this workbook to suit your preferences. For now, le
t's review the components included
in this workbook. Let's start at the top. The color of the bar can be customized to
personal preference. On the left side, there's the Excel logo,
followed by five icons called Quick Access Toolbars that begin with AutoSave. By clicking the on/off button, you can enable
or disable the auto save feature. And then, there's a Save icon that allows
you to manually save data. To the right of these icons are the Undo and
Redo options; in the next chapter, I'll explai
n these in further detail. Next, there's an arrow icon that enables you
to choose which command icons should appear in the Quick Access section. Simply select the desired commands. The Workbook name is displayed adjacent to
this. Moving to the right, there's the Search Bar,
which you can use to search through the Workbook. After that, there's the User Account. Lastly, there are buttons to minimize the
Workbook screen, adjust the display of the Workbook window, and close the Workbook. The section
beneath the green bar is known
as the ribbon, which stores nine tabs containing collections
of various command buttons for operating Excel. Upon opening Excel, the Home tab will open
by default, as it is now. Within the Home tab, you'll find a clipboard
group which contains copy & paste commands. There are also font groups, alignment group,
and several other groups. Please notice that by clicking the small arrow
next to a group name, additional command buttons that are not visible
on the ribbon
will appear. Apart from the Home tab, there are eight other
tabs that contain as well a variety of command buttons. Over the course of this tutorial, you will
become familiar with most of these tabs & their command buttons. Now, let's navigate to the File tab. Here, you'll find several other tabs, such
as New, for creating a new workbook. And Save, for saving your work. There is also Options for adjusting Excel
settings, such as General for several setting options like Fonts. And Colors setting
. There are also options for Formula setting,
Ribbons, and Quick Access Toolbar. As you continue through the tutorial, you
will gain a better understanding of most of these features. In the top right corner of the ribbon, there
are Comments & Share boxes that we will cover in our upcoming video tutorial. At the bottom right of the ribbon, there is
a downward arrow that allows us to adjust the ribbon's appearance. We can choose to go Full Screen. To display only Tabs. Or to display the Ribbon and
all Toolbars. There is also an option to display or hide
the Quick Access Toolbar. Beneath the ribbon is a white bar called the
Formula Bar, which displays the contents of the selected cell. Like this as an example. By clicking the downward arrow, we can expand
the display of the formula bar. Moving to the left, we find the feature for
inserting formulas, followed by a box containing the name of the
selected cell. The three dots are to widen the view of the
box. The main part of workbook is wor
ksheet, also
known as a sheet, is where we will be working with data and
formulas that will be covered in the next main chapters. Depending on our requirements, a workbook
can contain a single or multiple sheets. The sheet name is located at the bottom and
is assigned by Excel by default. By right clicking on this section, we can
access a menu of options to create new sheet. To Delete. Or to rename a sheet as necessary. We can move or copy a sheet. Additionally, there are options to display
code
and sheet protection, change the color of tab sheet. Hide a sheet. and select the entire sheet. The quick way to insert a new sheet, is by
clicking on the plus sign. The sheet selection can be moved using the
right and left arrows and three dots next to the sheet tab,
and we can jump from one sheet to another by right clicking on this arrow area and selecting
the desired sheet. A sheet consists of numerous cells, where
data such as numbers can be entered. If the data is in multiple cells, this
collection
of cells is referred to as a data range or range. A cell represents the intersection between
a column and a row. Each sheet has columns from A to column XFD
and more than 1 million rows. cells are named with a combination of column
and row names, such as A1 or B1, which can be seen in the name box. To select a cell, we have two options. Click the left or right arrow. Or we can use the mouse to move from one cell
to another. To select a range, we can hold down the Shift
key and click t
he right or left and up or down arrow. In case we have a large amount of data that
spans across multiple screens, we can use the scroll wheel of mouse to scroll
the screen. The row scroller is located on the right side
of the sheet, while the column scroller is at the bottom. Adjacent to the column scroller, three dots
can be used to lengthen or shorten the scroller width. The status bar, located on the bottom left
side of the screen, provides information about the status of our work on the shee
t. On the right side, there are sheet display
options, including Normal, Page Layout, and Page Break Preview. Additionally, there is a screen size adjustment
slider. With continuous usage, you can become more
familiar and adept with all the features mentioned above. Excellent! We are moving to the first main chapter, Raw
Data. There are three primary Data Types that can
be entered into a cell. Text, Date & Time, and Number & Percentage. Here are examples of text. A general text. Text is combined
with dates. And combination with numbers. And to input a number in text format, add
an apostrophe before it. Similarly, to type a date as text, add an
apostrophe before it. By default, the text format is displayed from
left to right as you can see. In below example, I will show you how to convert
a number to a text . First, right click the mouse and select Format
cells in the dialog box. In the Format cells box, choose the Number
tab and in the Category section select Text. And press Enter to c
onvert the number to text. I shall now explain how to write Date & Time. There are six formats that I am going to show
you. The first format is where the date precedes
the month, and it appears as dd/mm/yy. In this format, dd represents the date, mm
represents the month, and yy symbolizes the year. For instance, the date February 9, 2024 is
written as 9 / 2. This format is commonly used, particularly
in Indonesia. The second format displays the month first,
followed by the date. It appears as mm
/dd/yy. For example. Even though the date comes first, the month
is still displayed first. The third format is d, space, triple m, space,
quadruple y. For example. The fourth format is long form of the date,
dd, space, quadruple m, space, quadruple y. For example. The next format includes days as well, and
it appears as quadruple d, comma, dd, space, quadruple m, space, quadruple y. For example. For writing time, the format is double zero
colon, double zero colon, double zero, as hours, minutes,
seconds. For example. Please notice that the Date & Time display
format is usually from right to left by default. Similar to text, the display format of date
& time, number, and percentage can be customized using the Format cells Box. There are multiple ways to access this option. The first method involves right clicking on
a cell and selecting Format cells from the dialog box, followed by navigating to the
Number tab. The second way is to click on the format option
through the small window and
explore various available options and click on more number
formats for more options. The third way is to select the format command
buttons located on the ribbon toolbar. The fourth method involves clicking the small
arrow located in the Number group on the ribbon. Finally, we can also click on the Format box
in the cells group and then select Format cells from the dialog box. And Here's an example of how to modify date
format in Excel. Begin by right clicking on the cell you want
to format. The
n, select Format cells. In the Format cells dialog box, click on the
Number tab. The Category section provides a range of options
from General to Custom. In this example, we will set the Date format. Select Date, and a sample of the format appears
to the right. The format displayed beneath the Sample represents
the original format of the cell. Below the sample, there's a type box displaying
various available options. Choose the format that fits your preference,
and the date format will change ac
cordingly. The format options available in this box depend
on the Locale (Location) settings in this section. By default, Excel displays the location you
have set in your Windows settings. However, you can select for a different location. For example, if you choose English (United
States), the format options in this box will change. After selecting the format you want, press
Enter, and the date format in that cell will update
accordingly. We are moving to the next data type. There are six format
s for representing number
and percentage. The first is the general number format. For example. The second format uses a thousand separator. For example. The third format includes decimal points. For example. The fourth format represents percentages. For example. The fifth format includes currency symbols. For example. Finally, the sixth format is the accounting
format. For example. The process of modifying the number and percentage
format can be done through the Format cells box. By selecting th
e Number tab and choosing the
Number option, we can add a thousand separator or establish a decimal point. This allows us to specify the number of digits
to display after the decimal point. And we can choose to use or not use a thousand
separator. Lastly, in this Negative Numbers box, we can
customize how negative numbers are presented. In this example, I have utilized the thousands
separator symbol with a dot and decimal with a comma, following my previously set Windows
settings. However, in so
me countries, the convention
is the opposite, using commas for thousands and dots for decimals. We can modify these settings in Excel by accessing
the Options menu through the File tab, selecting Advanced options, and choosing the
Use System Separators option to adhere to the initial Windows settings. Alternatively, if we do not select this option,
we have the flexibility to modify the thousands and decimal separator symbols. To format decimal numbers, we can also click
on the Comma symbol avail
able on the toolbar. To increase or decrease the decimal places,
you can use the buttons next to it. For percentages, simply click on the Percent
symbol. Accounting formatting can be set by clicking
this command button and select the appropriate format. OK, That’s all about data types. Moving forward, let's explore various data
entry methods. There are seven methods to performing Data
Entry, starting from the Typing to utilizing Formulas. Let's begin with the first method: Typing. Using this met
hod, we can simply type the
required data. For instance, to create a sales data, we first
create titles in three columns. OK. Then we can begin typing the data. For example. In the event of incorrect data entry, it's
possible to make edits by selecting the cell in question and pressing F2 to modify its
contents. To undo previous work, simply press Undo button. Conversely, to restore any previously undone
work, press Redo button. Ok, let's continue with data typing. Done. As for date, we can modi
fy its format as we
have learned previously. And we can also change the decimal format
to one digit after the comma for numeric entries. OK. Now, let's proceed to the second method, Copy
& Paste Method. To do this, we need to select pre existing
data. For instance, let's copy data from the previous
Worksheet. After selecting the data, we can press the
Copy button. Next, we need to choose the cell where we
want to paste the data and press Enter. This will successfully copy the data into
the selec
ted cell. To copy the same data in multiple cells, select
the cell of the data to be copied, then select the target cell. Then click the Paste button as many as needed. Please notice a small arrow button on the
Paste command. By clicking the button, we can see several
paste options, including pasting data along with formatting and formula,
pasting only the formula, pasting only the values, or even pasting as an image. And we can also click Paste Special for more
options. Select desired paste opt
ion and click OK or
press Enter. Finaly, we can move data from one cell to
another. Select the data and click the Cut button. Then select the destination cell and press
Enter. This transfers the data to the new cell. OK, that’s all the basics of copy and paste. Now let's move on to learning about Flash
Fill. Flash Fill is a powerful tool that allows
for the automatic population of data based on user defined patterns. This feature saves considerable time when
filling out a Worksheet. For instance
, we can use Flash Fill to separate
the city name from the country name. To do this, we first create a pattern in this
column. Next, in the second row, we type first letter
of Berlin and press Enter. As a result, all city names are separated
in this column. Let me give you one more example. Apart from the first way, there are 4 other
ways to use Flash Fill. We can access it through the Fill button on
the Home tab, click Flash Fill. Or via the Flash Fill button located in the
Data tab. Or we can
drag the cell down, and click the
small box on the right, then click Flash Fill. We can also use the shortcut. Ctrl + E to initiate Flash Fill. With Flash Fill, we can not only separate
data but also add words after the city name. And we can also combine columns. Ok, that concludes our learning of Flash Fill. Let's move on to the fourth data entry method,
Custom List. A Custom List is a self created list of data
that facilitates the typing of data, saving time and minimizing errors. An example o
f a Custom List is a list of city
names or days and months. To create Custom List, click on the File tab,
select Options, then Advanced, and scroll down to the General section. Here you will find an option to edit custom
lists. Click on this button, a dialog box will appear. Select New List on the left, and then type
a list of data in the right box. Under the new list text, you will find the
days and months' names, which Excel provides by default. At the bottom, you will find an import data
sect
ion to create a new list without typing. For instance, to create a new list of city
names by importing them, press the arrow, select the city names list,
and then click Import. The city name list is now ready for use alongside
the Custom Lists of days and months. Here's how to utilize a Custom List: Let's
say we need to create a list of cities in column A,
instead of typing them individually, we can write Jakarta comma Indonesia in the first
cell, then drag the cell down starting from the
first
cell., and the list is automatically generated. The same method can be applied when creating
a list of days or creating a list of months
That concludes the topic of Custom List. We will now move on to Data Validation. Data Validation is a valuable tool to limit
data entry into cells based on specific criteria, which can prevent input errors and expedite
the data input process. For instance, when completing the Salesman
data with their corresponding cities in column B,
manually typing each city n
ame is time consuming and prone to errors. This is where Data Validation comes in handy. To utilize this tool, we first create a list
of city names outside the input data range, say in column G. We copy the list from previous list. Once the list is there, we then block the
cells in the column we want to fill. Next, we navigate to the Data tab and select
the Data Validation option. In the dialog box, we choose the list option
for the criteria and select the list in column G as the source. Click O
K. All the cells that we blocked earlier turn
into a dropdown list. To fill in the cell, click the arrow next
to the cell, then select the desired city from the dropdown options. This feature simplifies the data entry process
and minimizes errors. If we attempt to type a city name not included
in the list, an error will occur. Thus, Data Validation is an effective tool
that streamlines data entry while preventing errors. Ok. Next, we will explore another data entry method,
Form. One alternative
method of data entry is through
the use of a form. Forms can be particularly helpful when you
need to input data with many columns, say more than ten. In the following tutorial, we will use a simple
dataset to help you understand the basics of using a Form. However, in reality, it is common to have
data with many columns. Before starting, we need to display the Form
button first, since Excel does not display Form buttons
in the ribbon or quick access toolbar by default. To do this, click on the
Customize Quick Access
Toolbar button on the quick access toolbar, which is represented by a small arrow. Then select More Commands and in the selection
box, click on the down arrow that located under the text choose commands from. And choose Commands not in the Ribbon. Scroll down to find the Form
And select it. Click the Add button next to the search box,
press OK, and the Form button will now be available
on the quick access toolbar. To use the Form, select one of the cells from
the available
data. Once selected, click on the Form button, and
the Form will appear on the screen. The left hand side of the Form displays the
column headers, while the adjacent white box is where the data is inputted. To navigate between the white boxes, press
the tab key. The scroll bar located next to the white box
is especially helpful if there are multiple columns to fill in. The Form displays 1 of 3 on the right hand
side to indicate the first cell row of three that has already been filled in. Undern
eath, there is a row of buttons. Click on New to create a new entry, Delete
to remove data that has already been entered, and Restore to revert an entry to its previous
version. The Find Prev and Find Next buttons are useful
for searching for data from previous or subsequent cells. Criteria can be applied to search for specific
data based on particular criteria. Finally, Close button is to exit the Form. To input the data, click on New, and then
proceed to fill in the required information in eac
h respective white box. If the last white box already filled in, press
Enter to update the data. So, as you can see, the data already updated
completely. You can continue to fill in new data or you
can exit the form by pressing the Close button. Occasionally, there may be instances where
the Form cannot be displayed. For instance, when we attempt to generate
a Form using this example data, we may encounter a warning message. In such cases, we can click the OK button
to proceed, and the Form will
appear. OK, this concludes our learning on Form. Now, let's move on to the final method of
data entry, which involves Formula. Formula is one of important method for creating
data through data entry process. It's worth noting that while Formulas are
often referred to as Functions, vice versa, these two terms have distinct definitions. A Formula is a mathematical expression,
whereas a Function is a preset Formula built by Excel to simplify its usage for end users. There are four methods to acces
s the Formulas
in Microsoft Excel. First, you can type the formula directly into
the cell if you have memorized it. The second method is by using the Insert Functions
button. Third, you can access the Formula by navigating
to the Formula tab, which groups various formulas based on their
respective functions. And you can use the Sum button to access the
Formula. In this sub chapter, we will first focus on
learning how to use basic mathematical formulas. The basic math formula that we will learn
a
re multiplication, division, addition, subtraction & Sum Function. I have assigned each formula a unique color
for easy reference. To illustrate, I have prepared a simple data
table, including Product columns, Product Prices,
and corresponding sales Quantities and Amounts for January and February,
as well as the Total Quantity and Sales for both months. While some cells have been completed, others
with assigned colors remain unfinished. Our goal is to input basic math formulas into
the designate
d colored cells, as indicated by their respective colors. Let's begin. First, we will calculate in this cell, the
number of sales for Product A in January. The method is to multiply
The Price by the January Quantity
We Enter formula like this: equal sign, cell B10, times sign, cell C10, then Enter. Then we calculate quantity February of Product
B by dividing February Sales
By Price. We type, equal sign, cell F11, division sign,
cell B11, then Enter. OK, now we calculate the Total Quantity of
Pro
duct A by adding The January & February Quantities,
type, equal sign, cell C10, plus sign, cell D10, then Enter. Next, for Product B, subtract
the total quantity of the two products from the total quantity of Product A. We type, equal sign, cell G12, minus sign,
cell G10, then Enter. And finally, we will use Sum Function provided
by Excel. Select cell H12 and press the Sum button. The sum amount appears. So, It's worth noting that, if Excel does
not provide this Sum Function, we must type the na
me of cells to be summed
up one by one, as we did earlier. That's why, as I said earlier,
Function is a preset Formula that makes calculations become easier. Let's explore other Formulas. In this first part of the Top Formula chapter,
we will examine 15 Excel Functions commonly used in constructing raw data. During the discussion of Formula, we will
primarily use the Insert Function key. Begin by selecting the cell where the Formula
result will be stored. Next, click on the Insert Function butto
n,
which will open the Insert Function window. At the top of this window, type the name of
the Function. Click on the Go and OK button, or press Enter
twice. This action will open the Function Arguments
window. Alternatively, in the Insert Function window,
you can locate the Function name in the dropdown list within the Category. The Most Frequently Used Functions will appear
in the box below for quick access, while selecting All will display all Functions
in alphabetical order. Furthermore, sel
ecting a particular Formula
group will reveal the Functions within that group. Let’s select Most Frequently Used and select
Round. Press Enter. In the Function Arguments window, enter the
required arguments into each white box. Use tab key to move from the top white box
to the bottom white box After all the arguments are complete, the
formula's result will appear here or in this part. You may verify the accuracy first before pressing
Enter or clicking the OK button to complete the process. Ok, L
et's begin. Round is useful for rounding decimal numbers
to a specified number of digits following the decimal point. In the Number argument, select the number
that requires rounding. In Num Digits argument, specify the number
of digits to be rounded off. Such as 1 or 0. Excel rounds decimal fractions greater than
0.4 to 1, while those less than 0.5 are rounded down to 0. Once everything is correct, click 'OK' to
complete the process. Sequence is used to create a sequence of numbers
in rows and/
or in columns. In the first example we will create a sequence
of numbers in rows. In Rows, type number of lines to be created,
such as 10. We leave the Columns blank. In Start, we type the first number in the
sequence, such as 1. Then In Step, type how much the numbers should
increase in each line, for example, if we type 2, the number 3 will
follow the number 1 in the second row, and so on. In this case we type 1. Then the result is like this. Note that the sequence will consistently appear
in
the same order when rows are deleted. And in the second example, we will use rows
and columns. In Rows, type 5. In Column, type 5. In Start, type 100. In Step, type 50. Then the result is like this. Average is used to calculate the mean value
of a set of numbers. In Number 1, By default, Excel provides a
range of cells containing the numbers you want to calculate. Or you can manually select the range of cells
whose numbers you want to calculate. Enter. The result is like this. Count is used to c
ount the number of cells
in a range that contain numbers. In Value 1, by default Excel provides the
range of cells containing the numbers you want to calculate. Or you can manually select the range of cells
whose numbers you want to calculate. Enter. The result is like this. Countif is used to count cells within the
range that meet specific criteria. In Range, select the range of cells whose
numbers you want to count. In Criteria, define the specific criteria
or condition to determine which cell
to count. For example, we intend to count the number
of cells containing the number 200. Then in this Criteria box we type 200. The result is like this. Max is used to return the highest value within
the range. In Number 1, by default Excel provides the
range of cells containing the numbers for which you require the maximum value. Or you can manually select the range of cells. Enter. The result is like this. Min is used to return the lowest value within
the range. In Number 1, by default Excel
provides the
range of cells containing the numbers for which you require the minimum value. Or you can manually select the range of cells. Enter. The result is like this. EOMONTH is used to return the end date of
the month before or after a given date. In Start Date, select the cell that contains
the date or month. In Month, type the number of months before
or after the Start Date. For example, if you type 2, the result will
be December 31st. If you type 0, the result will be February
29. And if
you type 3, the result will be May
31st. OK, finally press Enter. Workday is used to calculate the completion
date of a job. It takes into account the start date, working
days and holidays. By default, this function defines weekend
holidays as 2 days. If you have different weekend holidays, you
can use the Workday.Intl Function that will be demonstrated in the second example below. Let’s continue. In Start Date select the start date of the
job. In Days select the number of working days
required
to complete the job. In Holidays, select the number of holidays
during which the work will take place, excluding weekend holidays. So as a result, the work is expected to be
completed on February 23 Utilizing the Workday.Intl Function is similar
to the standard Workday Function. However, it requires us to specify the number
of weekend holidays. In this example, we'll enter the Formula directly
to demonstrate how to determine weekend holidays. Firstly, let's select the Start Date. And Working Da
ys cell. Next, we'll need to select the Weekend Number
from the given list. We'll choose option 11 since there are only
Sunday weekend holidays. We'll then select the cell containing the
holiday dates and close the bracket. The result displays February 21,
which is two days faster than the previous example because there is only one weekend
holiday. Left is used to extract a specified number
of characters from the beginning of a cell, based on the provided arguments. The extracted characters may
include spaces,
punctuation, and delimiters. In the Text, we select the cell containing
desired characters. In Num Chars we type number of characters
will be extracted. Suppose we want to extract the first 3 characters
"The". We type 3. The result will be like this. Mid is used to extract a number of characters
in the middle of a cell by providing the function with the starting
position of the character to be extracted as well as the length of the character. In the Text, we select the cell conta
ining
desired characters. In Start Num we type the starting position
of the character to be extracted. For example, we want to extract the characters
of "Excelore", which the letter E is in the 5th position
of the cell, then we type 5. In Num Chars, type the length of the character
to be extracted. In this example 8. Then the result will be like this. Right is used to extract a specified number
of characters from the end of a cell, based on the provided arguments. In the Text, we select the cell
containing
desired characters. In Num Chars we type number of characters
will be extracted. Suppose we want to extract characters of "Channel''. We type 7. The result will be like this. Textjoin is used to concatenate characters
found in multiple cells and by using delimiter. In Delimiter, we type opening quotation marks,
a space, a delimiter such as asterisk, a space, and closing quotation mark. In Ignore Empty, leave it blank so that the
function ignores empty columns should they exist. Then
in Text 1, select the cells containing
characters to be concatenated. Then the result will be like this. Vlookup is used to search for a value in a
range of data by using specific keyword. In this example, we are searching for the
Category of Product A in this range. So the keyword is Product A. In the Lookup Value, select the keyword. In the Table Array, select the range containing
the keyword and the value we are searching for. The column where the keyword is located must
be in the first posit
ion of the selected range. And we need to lock this range selection to
prevent it from shifting when copied to another cell, by using the dollar sign or by pressing
F4. Then in Col Index Num, type the column sequence
number of the value we are searching for. In this instance, the Category column is in
the 3rd position of selected range. So we type 3. Then in Range Lookup, type false, which ensures
that the function only searches for exact matches. The result will be like this. Hlookup and Vlooku
p share similarities, but
there is a key difference. While Vlookup uses column sequence numbers
to find the target value, Hlookup uses row sequence numbers to achieve
the same result. In this example, we are searching for the
Product of Warehouse B in this range. So the keyword is Warehouse B.
In the Lookup Value, select the keyword. In the Table Array, select the range containing
the keyword and the value we are searching for. The row where the keyword is located must
be in the first position o
f the selected range. And we need to lock this range selection to
prevent it from shifting when copied to another cell, by using the dollar sign or by pressing
F4. Then in Row Index Num, type the row sequence
number of the value we are searching for. In this instance, the Product row is in the
3rd position of selected range. So we type 3. Then in Range Lookup, type false, which ensures
that the function only searches for exact matches. The result will be like this. Now that we have completed our
learning of
Top Formula Part 1, it's time to move on to the basics of Formatting,
which we will explore through these five topics. Let's begin. Let’s use this sample of simple data to
illustrate how Formatting can enhance its appearance. Currently, the font style I use is Arial with
a font size of 12. However, you may change the font style to
a type that suits your preference in this small window. Similarly, you can adjust the font size in
this box. If you desire to maintain uniformity in the
f
ont style and size across all new Workbooks, you can set it under the File tab, selecting
Options, then General, and finally, under the When Creating New Workbooks
section, adjust the font style and size as desired. Let's modify the appearance of this data range. Firstly, we'll make the data title bold. Italicized, and underlined. Next, we'll make all column headings bold. As well as the total section too. We'll also give color to the column titles. Let’s choose this color. And total column. We
choose this color. And for the total section. We choose this color. To make it look more organized, we'll place
the column title in the middle of the cell using these commands. The Middle Align will help make the text centrally
aligned in a row, while Center will make it in the middle of the column. We’ll apply the same for Salesman Name & Joined
Since columns. Now the data range looks neater. Additionally, we'll place the data title in
the middle of the data range. There are 2 ways to set it. F
irst, by using the Merge & Center button. Or we can set it through Format Cells box. Click this arrow, then select Alignment. Under Horizontal, click the dropdown list,
then select Center Across Selection. Well, now the data range looks better. Lastly, I want to demonstrate the Format Painter,
which is useful for quickly copying the format of a cell or data range
and applying it to other cell or data range, without changing the data contents. This is other data range with different contents. We'
ll select the above range, click on Format
Painter, and then apply it to this range. So, the format changed without changing the
contents. OK, that’s all about basics of Formatting,
we'll continue now to discuss Table. Now we learn the basics of Table through these
5 things. If you find data that is improperly formatted
on your sheet, it may be necessary to adjust the row height and column width. To do this, place the cursor at the right
end of the column, until it forms a plus sign. Then, while
holding the cursor, drag it to
the right until the column width is appropriate. Similarly, to adjust a row's height, place
the cursor at the bottom end of the row, hold the cursor, and drag it downwards to adjust
the height accordingly. It's also possible to adjust the column width
or row height by placing the cursor on the right side of the column and double clicking. This technique can be applied to each column
or row individually, or it can be executed simultaneously
by clicking the intersec
tion point where the column and row meet. And after that, select one of the columns
and double click. The same process applies to adjusting row
height. Now, we set border line for this data range. First select the range. Then proceed to the Format Cells box and click
on Borders. Here, you can choose the line type and color
you prefer. On the right side of the box, you can select
the border format either by using a Preset Border or by configuring it field by field. In this example, we use this li
ne type and
select the Outline option. For the border inside the range, we choose
this line type and select the Inside option. Finally, click OK, and the data range has
been changed with the set border format. In case you need to add more data, you can
insert new rows or columns. Choose the row or column to be inserted. Click Insert button, and then select Insert
Sheet Rows for the new row. To insert columns, click Insert button, and
then select Insert Sheet Columns. Conversely, to remove rows,
select Delete,
and then select Delete Sheet Rows. And to remove columns, select Delete button
and select Delete Sheet Columns. When working with extensive data that surpasses
one screen, it's vital to freeze column title rows, data names, and the first order columns. This ensures convenience and ease of use. To freeze the selected cells, select View
tab and select Freeze Pane. This feature will lock the column titles and
data names in place, allowing for scrolling or sliding of the screen
while
retaining the frozen items on the screen. Another way to do data range formatting is
to convert the range to a Named Table. To do this, select the data range outside
of the table name and choose Format as Table. Click to select the preferred table shape. In the dialog box that appears, click OK. The data range has been converted to a Named
Table. Named Table plays a critical role in Power
Pivot & Power Query features that we will explore in future videos. With that, we conclude our discussion of
Table
basics and let’s move on to the Validation technique chapter. Excellent! We are moving to the second chapter, Validation. Filtering Sorting and finding are basic features
that are useful for data Validation. With this in mind, let's delve into exploring
these four features in this sub chapter. To filter data, start by selecting a cell
and click on Sort & Filter button. Next, select Filter. To filter a specific column, click the arrow
located in the column header. In the dialog box, you ca
n select Filter by
color to filter based on cell color. You can use Text Filters. Or you can filter specific data from the list
in this box. For example, you can filter data for Salesmen
A and B. To remove a filter selection, click the arrow
once again and select Clear Filter From. To deactivate Filter, click Sort & Filter
button once again and select Filter. Now we explore sort feature. To sort the data, first select the column
containing the data to sort. Click on Sort & Filter button, which p
rovides
three sort options, Sort Smallest to Largest, Sort Largest to Smallest. And Custom Sort. let’s choose Custom Sort. Here you can specify the column to sort, the
object to sort, and the sort order. Click ok to complete the process. Alternatively, sorting can be done via Filter
dialog box which provides three sort options. OK. We move to Find & Replace. To search for data, you can use Filter dialog
box and type the data to search for in this box. However, the search is limited to data withi
n
a column. To search for data across the entire data
set, select Find & Select button and choose Find. enter the data to search for in Find What
box, say Salesman B. And press Enter or click Find Next. This will direct cursor to the cell containing
Salesman B. And to replace the data, select the Replace
tab, enter the data to replace in Find What box,
for instance Salesman BBB and type new data in replace with box, for instance Salesman
B. And then click Replace All button or Replace
button. Th
en click Close button. OK, next, we will discuss useful Formulas
for data Validation. In this second part of Top Formula chapter,
we will explore additional 6 Formulas that are useful in the basics of data Validation. Let's begin. Len is used to count the character length
of a text, including spaces. In Text, select the cell containing text whose
character length you want to count. Click OK. And the result is like this. Trim is used to remove unnecessary spaces
in a text. In Text, select the cel
l containing text whose
unnecessary spaces you want to remove. Click OK. And the result is like this. Upper is used to convert the text to capital
letters. In Text, select the cell containing text you
want to convert. Click OK. And the result is like this. Lower is used to convert the text to lowercase. In Text, select the cell containing text you
want to convert. Click OK. And the result is like this. Proper is used to convert text to proper case
where the first letter is capitalized, and the r
emaining letters are in lowercase. In Text, select the cell containing text you
want to convert. Click OK. And the result is like this. Replace is used to replace part of text with
different text. In Old Text, select the cell containing text
you want to change. In Start Num, type position of first character
you want to replace. In Num Chars, type number of characters you
want to replace. And in New Text, type the text that will replace
old characters. Click OK. And the result is like this. Ok, w
e will move on to Go To Special. Go To Special enables cursor to locate specific
cells based on user defined criteria. To use this feature, select data range first. And then click on the Find & Select button,
followed by Go To Special. This provides criteria options such as Notes,
Constants, Formulas, and more. However, the most commonly used criteria is
Blanks, which identifies empty cells that require correction. Let’s select Blanks and click OK. As can be seen, cursor highlights all blank
cel
ls, making it easier to correct them by populating
them with the appropriate data or deleting them altogether. OK, let's shift our focus to the issue of
mistake and error in next chapter. We will learn five Common Errors that often
occur when working with Excel and how to handle them. Let's begin. #Div Error occurs when dividing a number by
zero or an empty cell. To correct this, adjust the zero number or
the contents of the cell. And to prevent this issue from reoccurring,
consider adding an IF
ERROR Formula into the existing one. Press F2 to edit formula. Type IFERROR at the start of formula. Type opening bracket. And after the existing formula, add semi colon
or comma as delimiter. Type the value you want to display in case
of an error. If you want to leave the cell blank, type
two quotes with nothing in between. Close the formula with a closing bracket,
and press Enter. By adding IFERROR, dividing any number by
zero, results in a blank output instead of a #Div Error. #Value Error oc
curs when applying a formula
in an unsuitable format, such as dividing cell containing number by
cell containing text format. To resolve this error, correct the cell that
contain text. #NA Error occurs when searching for data using
a mistyped keyword. For instance, the keyword Product B has double
spaces between the words Product and the letter B.
To improve the formula, correct the keyword writing. #NAME Error occurs when the function name
is inaccurately spelled. In this formula, for instance,
the VLOOKUP
Function is spelled with a single letter O. Correct the typo to resolve the error. #Ref Error occurs when the formula tries to
search for data in a column where it doesn't exist. In this instance, the formula indicates a
Column Index Number of 3, whereas, due to the range of data used, A4
to B6, there are only 2 columns. To resolve this issue, the Column Index Number
should be corrected. OK, all errors already corrected, we can move
now on to the next chapter to learn about data Ana
lysis basics. Excellent! we are moving to the third chapter, Analysis. Conditional Formatting is an effective feature
for highlighting significant data points within a range. This tutorial will provide basic insights
into Conditional Formatting through four illustrative examples
that will equip you to begin your data analysis journey. In the given data range example, we intend
to highlight specific numbers within four columns using Conditional Formatting. Firstly, we aim to highlight sales value
s
exceeding 7,500 in column B. To do this, click on the Conditional Formatting
button and then select Highlight Cells Rules. Choose the Greater Than option from the list. In the Greater Than box, input 7,500 as the
rule criteria. From the dropdown list on the right, select
the desired cell format to be displayed if the rule criteria is met. Various formatting options are available,
including the Custom Format. Let’s use this option. Format Cells box opens, allowing us to customize
the formatting
to our preferences. In this case, we can select a bold italic
font format in white. And for the cell, we choose red. Click OK twice to apply, and the results will
be as shown in column B. And in column C, we will highlight sales values
that fall below 5,000. Click on the Conditional Formatting button
and then select Highlight Cells Rules. Choose the Less Than option. In the Less Than box, input 5,000 as the rule
criteria. And then we choose default option for cell
format. Click OK. The results
as can be seen in column C.
In column D, we will highlight the Top 7 sales values. Click on the Conditional Formatting button
and then select Top Bottom Rules. Choose Top 10 Items. In the Rank box, input 7 as the rule criteria. And then we choose default option for cell
format. Click OK. The results as can be seen in column D.
And finally, in column E, we will highlight the sales values that are above average sales
value. Click on the Conditional Formatting button
and then select Top Bottom Rule
s. Choose Above Average. We only need to choose cell format, and we
choose default option. Click OK. The results as can be seen in column E.
That concludes our discussion on the basics of Conditional Formatting. Next, we will dive into Part 3 of the Top
Formula. In this third part of Top Formula chapter,
we will explore 2 Formulas that are useful in the basics of data Analysis. Let's begin. IF is used to assign values to data from a
cell based on specific conditions. If the data in the cell meet
s the condition,
a certain value is given, and if not, a different value is given. For instance, if sales number in column B
exceed 3500, then 15% bonus is given in column C.
Otherwise, if sales are below 3500, then 5% bonus is given. To do this, in Logical Test, type sel B5 greater
than 3500 as the conditions for receiving 15% bonus. Next, in Value If True, type 15% as bonus
if sales number in kolom B exceed 3500. And in Value If False, type 5% for sales under
3500. Press Enter or OK button. Co
py the formula into the cells below. The result is that only Salesman B receives
a 15% bonus since his sales exceed 3500. Index & Match is a commonly used alternative
to Vlookup because Vlookup is incapable of searching for data to the left of keyword. Index Function is used to locate data in search
column that matches the provided keyword, whereas the Match Function is used to determine
the column in which the keyword is present. This keyword column can be to the left or
right of the search col
umn. For instance, suppose we intend to identify
the Category of Product B in this range. In this case, Product B will be the keyword. The Function should be directly entered into
the cell, rather than the Function Arguments box. We begin by typing equal sign. Type Index. Then opening bracket for Index Function. Select the search column where Product Category
B is located. Semicolon. Type Match. Opening bracket for Match Function. Select keyword. Semicolon. Select the keyword column. Semicolon.
Select 0 for Match Type. Closing bracket for Match Function. Closing bracket for Index Function. Press Enter. The result is like this. OK, with that done, let's proceed to the chapter
on Chart basics. Charts are a powerful tool for visualizing
the results of data analysis in a form that is easy to understand. This tutorial offers basic insight on Charts
through Bar Chart & Pie Chart models based on this simple data example. Let's get started. In the first example, we aim to generate a
bar chart
that visualizes the monthly product sales. Select data range first. Then click Insert tab. Then select Bar Chart from the Chart command
group. In the Chart Form options, select this one. Click. Then the chart has been created. We can adjust the size of the format by using
cursor to drag its edges. The chart comprises several components. Such as Chart Title. Value axis. Series, in this example Product. Category axis, in this example months. And Legend series. Formatting options for each component
can
be customized, such as making the Chart Title bold
or changing the color of a specific bar, such as Product C to yellow. The Chart Title can be modified accordingly,
such as changing it to Monthly Sales by Product. Additionally, sales figures can be added to
each bar. Hover the cursor over the plot area until
the cursor forms a plus sign. Click. Select the Chart Design tab. Select Add Chart Element. And choose Data Labels. Swipe right to choose a label position that
suits our preference. OK
In the second example, we will create a Pie Chart to visualizes sales contribution by
product. Select data range first. Then click Insert tab. Then select Pie Chart from the Chart command
group. In the Chart Form options, select this one. Click. Then the chart has been generated. Let’s add the percentage contribution. Hover the cursor over the plot area until
the cursor forms a plus sign. Click. Select the Chart Design tab. Select Add Chart Element. And choose Data Labels. Choose label position
. And change font size bigger. Make it bold. OK, that conclude the basics of creating Chart. We will be learning about Pivot Tables in
the next chapter. Pivot Table is a powerful tool for efficient
data analysis and reporting. It enables the addition and detailing value
numbers with accuracy and speed. To illustrate the significance of Pivot Table,
let's take this example dataset. If you're tasked with finding the sales total
for Category A in 2022, along with the product details,
traditional me
thods would require creating formulas and filters, which can be time consuming. Furthermore, obtaining similar details for
other Categories would require additional effort. However, by using a Pivot Table, these tasks
can be done with ease and efficiency. To start a Pivot Table, select the data range,
followed by clicking on Insert Table. Click on Pivot Table, and in the dialog window,
select New Worksheet, and then press Enter. This creates a Pivot Table in new Sheet with
an editor located on t
he right for assembling Pivot Tables. In the second box, you'll find the column
titles of data range you selected earlier. Below them are four boxes for configuring
the data's appearance. The goal is to select the data from the second
box and assembly it into these four boxes. For instance, we add the Year to Column to
facilitate yearly comparisons, while Sales number added in Value. Category and Product can be added to the Row,
while Salesman and City can be added to the Filter. Upon completion
, the Pivot Table will be visible
on the left side. Close the editor and commence using the Pivot
Table. As per our arrangement, the Year data has
been placed in the column, with Category and Product on the row. Salesman and City have been set as filters. Let's make some changes to the format. Change column title wording. And change the number format to comma style. Next, click on Pivot Table Analysis tab, then
select Options. Choose Display. Check the Classic Pivot Table Layout to enable
field
dragging. Enter. With this Pivot Table, we can easily obtain
the total Category sales per year, including the Product breakdown. We can also determine the total sales per
Salesman or City, with a breakdown by Category and Product. To do this, simply click on the Salesman filter,
select a specific Salesman such as Salesman A.
And we can revert back selecting all salesmen quickly. To display sales by Salesman by Category & Product,
select the Salesman button on the filter field, and drag it down i
nto the data field. Now, sales by salesman are available. If you want to display other data, select
a cell in the Pivot Table, right click, and choose Show Field List. Now, you can add Country to the filter. You may choose to display or hide subtotals
by right clicking on the data. And click Sub Total Category. Ok, that concludes the first part of the lesson
on Pivot Tables. In part two of the Pivot Table chapter, we
will learn about Slicer and Pivot Table Charts that are useful to generate repo
rts. Excellent! we are now moving to the fourth chapter, which
is the Report. In this part, we will delve into Slicer and
Pivot Charts. Slicers are powerful filter tools that enable
effective data analysis. To access the Slicer, click on Pivot Table
Analysis tab and click on Insert Slicer. You may decide to select only the relevant
fields required or to select all fields. For instance, in this example, we select Salesman
and Category fields. Then click OK. Let’s first set the Slicer's location.
And adjust the box size. With a Slicer, data filtering becomes a breeze. A single click on the Slicer instantly displays
the relevant data in the Pivot Table. To enable multi field filters, click the Multi
Select button. Now we can do filtering for Salesmen A and
B only. And filter for Categories A and C only. Ok, let’s move on to Pivot Chart. Pivot Charts is a chart that is generated
based on data set in a Pivot Table. With this kind of chart, making changes to
chart data and filtering displaye
d data in the chart become easier. To do this, select one of the cells in the
Pivot Table. Then click on Pivot Table Analysis tab. Select Pivot Chart. Then select the Type, such as Clustered Column. Then Enter. The chart appears with the data arranged as
in the Pivot Table. Modifications to the Pivot Table, such as
returning Salesman to the filter field, will reflect in the chart as well. We can also do filter data directly on the
chart. For instance, click City and select Jakarta. Then the char
t changed. We can also move this chart to another sheet
by copying and pasting. OK, that’s all about Pivot Tables, we will
move on to creating report with Simple Template. Before delving into report creation, let's
first explore the fourth part of the Top Formula chapter,
which comprises two Formulas that can prove useful in generating reports. Let's begin. Xlookup is used to search for data in multiple
columns by just typing a formula once. For example, we aim to obtain Category & Sales
of Prod
uct B in this range. And the results will be returned in cells
F4 & G4. We can do it by creating the formula only
in cell F4. In Lookup Value, select keyword. In the Lookup Array, select the column containing
the keyword. In Return Array, select the Category column
& Sales column. Leave If Not Found & Match Mode blank. Then Type Enter. The result is like this. Sumif is used to add up the values in a specified
data range column for the given keyword. In the Range, select the column where the
keyw
ord is located. In Criteria, select keyword. In the Sum Range, select the column where
the values for the keywords you want to sum are located. Press OK button. The result is like this. OK, with this result, we can move on to the
next chapter to learn how to create a simple report template. Static report templates are frequently used
in offices or other institutions. This is example of simple template for Sales
Summary Report that generated based on the source data in Sheet List. We will redo th
is report by completing the
parts that have not been completed yet such as formatting & numbers. Let's begin. Our first task is to modify the report title's
appearance. Select the title area. And click Merge & Center. Then, we will add borders to the Product Summary
section. Select all areas. Then click little arrow. Click border. Select the line style. Then move down to choose border color. Select Outline. And then select Inside. Enter. Next, we will narrow the width of column B
& column L. Cli
ck on both columns. Then place the cursor at the end of either
column. Swipe left while holding the cursor until
desired width is achieved. OK. Next, fill in cells H10 to H14 with Categories
based on Products on the left. We will use VLOOKUP Formula to retrieve data
from the List Sheet. Select cell H10. Select Insert Functions. Select VLOOKUP. Click OK button. In Lookup Value, filled in with keyword, in
this case Product A in cell G10. In Table Array, filled in with range in Sheet
List that cont
ains keyword & Category data. Go to Sheet List. Select cell B3 & C3. Block until row 93. Lock with F4. Then in Col Index Num, filled in with 2. And in Range Lookup, type 0 or false. Enter. Copy the cell. And paste to cells below. OK
Next, fill in cells I10 to I14 with total sales numbers of Products on the left. We will use Sumif Formula to retrieve data
from the List Sheet. Select cell I10. Click Insert Function. Select SUMIF. Click OK button. In Range, filled in with range in Sheet List
that c
ontains Product. Go to Sheet List. Select cell B3. Block until B93. Lock with F4. In Criteria, filled in with key word, in this
case Product A in cell G10. In Sum Range, filled in with range in Sheet
List that contains sales number. Go to Sheet List. Select cell H3. Block until H93. Lock with F4. Enter. And then copy. Paste to cells below. OK. Next, select cell I15 and filled in with sum
of sales numbers. Click Sum Function button. Enter. Check if the numbers already correct or not. OK, the numb
er is correct. Finally, fill in the Country & Zone columns
using the XLOOKUP Formula. Select cell H20. Type equal sign. Type XLOOKUP. Opening bracket. Select cell G20 as the keyword. Semicolon. Go to Sheet List. Select the column containing City at cell
E3. Block until row 93. Lock with F4. Semicolon. Go to columns that contain Country & Zone. Select F3 & G3. Block until row 93. Lock with F4. And then closing brackets. Enter. Return to cell H20. Copy & paste into the cells below. OK, we have suc
cessfully completed our report. Let's learn how to print this report in the
next chapter. Let's conclude our tutorial by covering how
to Print, Protect Data, and Save a Workbook. To print this report template, we begin with
setting print area to exclude any unprintable columns. Select Template range. Then click the Page Layout tab. Press Print Area. Two options available, to set a new print
area and to cancel the previously set print area. Click Set Print Area. After that go to the File tab. Sel
ect Print. This is setting printing & pages window. Set the number of times the page will be printed. Then in the Settings area, you can set which
sheets will be printed. And set printing output whether printing sequentially
or one by one. In orientation settings, let’s choose Landscape
as the template is in landscape shape. We can choose paper type here. Then there are margin settings. If you select Custom Margins then the Page
Setup window will open. Then scale settings. And below there is a l
ink to Page Setup. Click. On the Page tab, we can set the Orientation,
Scale & Paper Type. On the Margin tab, we can set Margin Size
and position for the report on the paper. We select center Horizontally & Vertically. We can add header & footer notes on the Header
Footer tab. On the Sheet tab, we can set the Print Area
& Print Title. And select preferences like Gridlines or Black
and White output. Once we have configured all the settings,
we can click OK. In the bottom right corner, there is a
button
for zooming. Next to it, we can display the margin guidelines. This margin guideline can be adjusted with
the cursor. OK. Then there is the Printer Set Up. Click. You can add a new printer device by clicking
Add Printer. If no printer available, the output will be
saved as a PDF file by default. In this case, if we click Print, a window
to save print output appears. Select a storage folder. Type the file name, then press OK. We will see the storage folder later. Now we continue printing t
he Sheet List. For the Sheet List, we go directly to Page
Setup by clicking the Page Layout tab, then clicking Print Titles. Page Setup window opens. On Sheet tab, set Print Area by blocking cells
A3 to H93. On Page tab, select Portrait Orientation. For Scaling, select Fit to 1 Page Wide to
ensure all columns visible on each page. Then select By 2 tall to print it on 2 pages. Then on Margins tab, select Center Horizontally
& Vertically. Next, On Footer Header tab, for the Header
we select page 1
of. For the Footer select file name. You may also customize header & footer notes
by clicking on these two buttons. Once done, click on the Print Preview button. Verify that all columns are visible on one
page and that there are two pages to be printed. But there is a problem where column titles
are not visible on the second page. To resolve, return to the sheet, press Print
Titles again. In Rows to repeat at top, fill in the range
of column titles by selecting the column title row. Then, click
Print Preview again. OK the Sheet List is ready to be printed. Next, to prevent unintentional deletion or
modification, we need to protect all cells within the data range, except those edited
frequently. To do this, select the intersection of cells
and columns. Then click formatting cells. Select the Protection tab. Ensure the Locked checkbox is ticked. Additionally, if you tick Hidden checkbox,
then cell contents will not be visible in the Formula Bar. On the other hand, if it is unticked, the
contents of the cell will be visible in the Formula Bar. In this case we tick Hidden. Then click the OK button. Now select a cell or range that is not protected,
for example this range. Then go back to Formatting Cells, select Protection
and unchecked Locked & Hidden. Then press OK button. Afterward, select this range. Then select Formatting Cells, Protection and
untick Hidden. And press OK button. Next step is going to Review tab and select
Protect Sheet. Type a password. Press OK. Confirm the
password, then press OK. To test the protection, select a cell within
the protected range and attempt to delete its contents. A warning message will appear, indicating
that the cell is protected and cannot be deleted. Additionally, the contents of this cell are
not visible in the Formula Bar as it was set hidden. Then move to this cell. The contents are visible in the Formula Bar
although it cannot be deleted. Then select this cell that are not protected. Ok, we can delete its content. To save
your data, simply click on Save button,
or alternatively, activate the AutoSave function. It's also possible to save the Workbook to
another destination or under a different name or file format. To do this, click File tab. Select Save As. Then in the Excel window, select the storage
folder. In the Save As window, type new name for the
file. After that press Save to continue or press
Cancel button. If you prefer to save the file in PDF or another
format, click Save as type, then select PDF. Unche
ck Open file after publishing. Press Save button. The PDF file is now saved in the designated
folder. To view the folder in the File Explorer, let's
first close this Workbook. On the screen, we can see that we have saved
3 files: the original file, PDF printed version, and PDF Save As version. If required, we can modify these filenames
by right clicking the file, selecting Rename, and typing in the new name. Now, let's open one of the PDF files. OK, the report looks good in the PDF format,
indic
ating we have succeeded. Excellent. We have successfully completed the tutorial. If you found it helpful in improving your
Excel skill, kindly subscribe, like & comment. We encourage you as well to explore our other
videos. Thank you. And have a productive day.
Comments