Skip to content

Excel Basic Tutorial: for Beginners to Experts

Search Content:

I’ve prepared this Excel Basic Guide as a tutorial for beginners that has been optimized and hopefully is suitable for many people.

For example, for teachers as learning material, students, or for those of you who are already working.

I’m sure you can master the Microsoft Excel program even if you have just started learning from a basic level.

Basically, everyone who is proficient in using Excel can do so thanks to perseverance in practice.

Note: Most of the content is available in Indonesian. Please visit the Excel Basics Guide!

“YOU CAN LEARN EVEN IF YOU DON’T KNOW AT ALL ABOUT MICROSOFT EXCEL”

Rolan Mardani

Chapter 1: Workbook

The Workbook chapter contains the most basic tutorial for learning Microsoft Excel. When using Excel, you must first know what a Workbook is.

Although most beginners understand that a Workbook is the same as an Excel file, that’s not all I’m going to discuss. Please choose one of the following 6 sub-chapters:

  1. Workbook: Understanding, Creating, Opening, Closing, and Managing Excel Workbooks.
  2. Saving in 97-2003 Format: Tutorial to save an Excel file to 97-2003 Format.
  3. View Multiple Workbooks: How to open multiple Workbooks simultaneously.
  4. AutoSave & AutoRecover: Forgot to save an Excel file? Use this method to restore it. Also, learn how to create automatic backup copies.
  5. Protection: Lock Excel files, Workbooks, sheets, cells, read-only, mark as final, and how to unlock Excel Password.

Chapter 2: Worksheets

If there is a Workbook, then there is a Worksheet. Generally, beginners also know a Worksheet as a sheet. Yes, that’s true…

But that’s not all you need to know. There are several tips and tricks about WorkSheets that you can maximize.

In fact… These tips and tricks are often used by Excel experts.

  1. Worksheets: Understanding, Adding, Renaming, Deleting, Moving and Copying Worksheets.
  2. Zoom: Zoom-in and Zoom-out Worksheets.
  3. Split: Split a Worksheet into 2 or more different views.
  4. Freeze Panes: Freeze column/row headings (Very useful if you have large amounts of data).
  5. Group Worksheets: Combine Worksheets for simultaneous viewing and editing.
  6. Consolidation: Combining data from different Workbooks/Worksheets into 1 Workbook/Worksheet.
  7. View Multiple Worksheets: View two or more Worksheets simultaneously.
  8. Get Sheet Names: How to find out the name of your Excel file and where to save It.
  9. Gridlines: How to remove and display the checkerboard lines that border each Excel cell.
  10. Spell Check: How to prevent typos with the Automatic Spell Check feature using the Excel dictionary or using your dictionary.

Chapter 3: Ribbon

In Excel, maybe you are familiar with menus such as Home, Insert, Formulas, etc. These menus are more suitable to be called Tabs (in Excel). All Tabs are in the Ribbon.

All versions of Microsoft Excel (2007, 2010, 2013, 2016, etc…) have a Basic Tab which by default you can use straight away.

However, for special needs, you can also create special tabs. I have several materials related to this Ribbon Chapter, as follows:

  1. Ribbon: How to organize Ribbon Tabs (Home, Insert, Page Layout, etc.).
  2. Quick Access Toolbar: Create a custom toolbar for quick access. (Example of data input form).
  3. Developer Tab: Enable the developer tab to create macros and learn Excel VBA.
  4. Status Bar: Get to know and create custom status bars.
  5. Check Box: How to make an Excel check box + a combination of check box and dynamic chart.

Chapter 4 – Column, Row, Cell, and Range

Chapter 4 will be quite long. There is a lot of learning material that you must understand as a basis for learning Excel.

When using Excel, of course, you have to know what Columns, Rows, Cells, and Ranges are. It’s not just that.

I also have some in-depth discussions about the tricks most frequently used by Excel Experts.

  1. Range: Get to know columns, rows, cells, and ranges with examples.
  2. AutoFill: How to fill a range automatically (Dates, Sequential Numbers, etc.).
  3. Fibonacci Sequence: AutoFill method for arithmetic series, such as Fibonacci Sequence, etc.).
  4. Custom Lists: How to create custom lists for AutoFill.
  5. Comments: How to make comments on certain cells.
  6. Hide Columns & Rows: How to hide columns and rows.
  7. Skip Blanks: How to skip blank cells for copy-paste.
  8. AutoFit: Automatically changes column width and row height.
  9. Transpose: How to convert columns to rows and vice versa.
  10. Flash Fill: How to automatically extract or combine data.
  11. Fill Justify: How to combine cell contents (can be 2 or more cells) into 1 cell.
  12. Move Columns: Various ways to move columns (Complete).

Chapter 5: Format Cells

You must understand the Cell Format features as basic knowledge in learning Excel. If you skip this chapter, I’m sure, you will find a lot of difficulties in using Excel.

  1. Format Cells: Change the units of a number without changing its value without a formula. For example, 1500 becomes IDR 1,500, etc.
  2. Decimal Place: How to round numbers and change the number of digits after the comma without changing the value.
  3. Date and Time Formats: How to use date and time formats.
  4. Fractions: How to enter fractional values in Excel.
  5. Currency vs Accounting: Difference between currency format and accounting format.
  6. Text to Numbers: How to change text format to number format and vice versa + Examples of VALUE and TEXT functions.
  7. Format Painter: How to easily copy formatting in one cell/range to another cell/range.
  8. Cell Styles: How to quickly change cell designs.
  9. Themes: Change Excel themes and create, save, and use custom themes.
  10. Wrap Text: Displays (long) text in several rows in 1 cell. (Often used in long text in narrow columns).
  11. Merge Cells: Combines 2 or more cells into 1 cell.
  12. Superscript, Subscript, and Strikethrough: How to create superscript (like 102), subscript (like H20), and strikethrough/text with strikethrough marks (like this).
  13. Check Mark: How to add a check mark (like ✔) in Excel.
  14. Border: How to create and remove lines like creating table lines with Border.

Chapter 6: Custom Number Format

If the number format feature (in Chapter 5) does not meet your needs, consider Custom Number Format.

In essence, you can use this feature to change the appearance of data (numbers or dates) without changing the data value itself.

With this, you don’t need to be afraid of errors occurring in the formula/function calculation process on your data. Excel still recognizes the data as valid numbers or dates.

Just a suggestion, it’s a good idea to first understand the basic rules for custom number formats which I have discussed in the first sub-chapter below:

  1. Custom Number Format: Definition, basic rules, how it works, and the arrangement of each code in Microsoft Excel’s Custom Number Format feature.
  2. Create Units: Add data units like kg, pcs, unit, degrees Celsius (°C), etc. without changing the data values.
  3. Large Numbers: Make abbreviations for large numbers such as 2,565,000 (two million five hundred and sixty-five thousand) to 2 million or 2.57 million.
  4. Prefix 0: Create custom numbers that start with 0 or +62 as well as examples of creating special cell phone number formats such as +62-821-2345-6789 automatically.
  5. Date & Time: Uses a special number format for data in the form of date and time.
  6. Conditional: Create a custom number format using specific conditions.

Chapter 7: Find & Select

If you want to search, select, or replace a lot of data at the same time, the Find & Select feature can make the job easier.

In other words, to act en masse, you can use the Find & Select feature. Try studying some of the following material:

  1. Find & Select: Search for text/numbers, change them in bulk, and search for cells that use formulas, comments, conditional formatting, etc. Everything was done quickly.
  2. Delete Blank Rows: How to delete blank rows (useful if there are unwanted blank rows in the middle of data etc.)
  3. Row/Column Differences: Marks cells that have different values in one row.
  4. Copy Visible Cells Only: How to copy-paste only visible cells (ignoring hidden cells)
  5. Search Box: How to create a simple (fast) search box.

Chapter 8: Templates

At first, you may be used to using a Blank Workbook when you start working with Excel. However, Excel also provides several special templates that you can use for certain jobs.

In this Template Chapter, I discuss in more depth how to use several special templates in Excel.

  1. Templates: How to use Excel’s default templates as well as save and use custom (self-made) templates.
  2. Calendar: How to create a calendar in Excel (365 days).
  3. Budget: How to create a Budget Report in Excel.
  4. Meal Planner: How to create a Dinner Planning template.
  5. Invoice: How to create a simple Invoice (invoice) template.
  6. Automated Invoice: How to create automatic invoices.
  7. BMI Calculator: How to make a BMI Calculator to check Ideal Body Weight.

Chapter 9: Data Validations

The Data Validations feature is useful for applying certain conditions to a cell/data range. For example, maybe you have come across an Excel file that can only be filled with certain data, or for example a drop-down list.

Excel experts often use this feature to maximize work using Excel. Even in the world of work, the Data Validation feature is very often used.

Luckily, you can learn all Excel Data Validation features from basic to advanced level (combination of formulas/functions) from the following sub-chapters:

  1. Data Validation: To fill cells with certain choices (Example: cell B2 can only be filled with numbers 1, 2, 3, or 4).
  2. Functions + Data Validation: 4 Examples of using formulas/functions in Excel Data Validation.
  3. Drop-Down List: Create Data Validation using a drop-down List.
  4. Dependent Drop-Down List: Example: if cell B1 is filled in on Wednesday, then cell B2 can only be filled in the afternoon or evening. If cell B1 is filled in on Thursday, then cell B2 can only be filled in in the morning or afternoon, etc.
  5. Kg to lbs: Validation example for converting Kg to pound units.

Chapter 10: Keyboard Shortcuts

If it can be faster, why not?

Now in Excel, there are several Keyboard Shortcuts to speed up your work. So you only need to use a combination of keys on the keyboard without touching the mouse/touchpad.

I’m sure it could be much faster!

For example the most basic Keyboard Shortcuts: CTRL + C and CTRL + V (aka Copy-Paste). Apart from these shortcuts, there are many other shortcuts that you can use.

  1. Keyboard Shortcuts: Shortcuts using the Keyboard (Basic, Moving, Selecting, Formulas, and Formatting.
  2. Function Keys: Keyboard shortcuts for F1, F2, F3, F4, etc…
  3. Insert & Delete Row: Keyboard shortcut to add 1 or more Rows and add specific Cells.
  4. Save vs. Save As: Keyboard shortcut for saving an Excel file (save) and saving with a new name (save as).
  5. Scroll Lock: Recognize and activate Scroll Lock with the keyboard.
  6. Bullet Points: Keyboard shortcuts for inserting symbols, bullets, or custom cell formats.
  7. Line Break: How to use Line Break.
  8. Show Formulas: Displays formulas in cells that use formulas.

Chapter 11: Print

By default, Microsoft Excel doesn’t look the same as Microsoft Word. Maybe you are already used to printing Word files and I’m sure you won’t have many problems.

However, it’s different if you use Excel, there are certain settings that you have to adjust so that the printed results match your wishes.

For example, printing only certain pages, back and forth, and preventing the printout from being cut off.

  1. Print Excel: How to print an Excel Worksheet. Example: printing certain pages or all of them or if you want to print pages that are too wide (can be adjusted to the paper size)
  2. Workbook View: View your Worksheet with different views (Normal, Page Break Preview, Page Layout, or Custom)
  3. Page Breaks: How to set page breaks (page break preview) as desired. For example, line 15 is the border of page 1, then line 16 will be printed on page 2.
  4. Headers and Footers: Create Headers (like Letterhead) or Footers (like footnotes).
  5. Page Numbers: Create page numbers in Excel.
  6. Print Title: Specifies the row and column to be printed as the header of each page. (Repeated Header/Repeated Letterhead, etc.).
  7. Center on Page: Print the Excel file to the center of the page.
  8. Print Gridlines & Titles: Include Excel’s default Gridlines (horizontal and vertical lines) when printing.
  9. Print Area: How to print only selected cells (range or special area).

Chapter 12: Share

One of the best features of Microsoft Excel is Share. You can manage Excel files using other software both online and offline. Apart from that, you can also import & export data from and to other software.

Maybe it’s not easy, but I’m sure you will get valuable insight from the following sub-chapters:

  1. Share & Embed: Display Excel data to Word files. The data will be synchronized so you can work on Excel files in Word.
  2. Excel to PDF: How to save Excel to PDF, tools that can be used, and problems when converting Excel to PDF and their solutions.
  3. Share Workbooks: Makes Excel files accessible and editable by more than one person at the same time.
  4. OneDrive: How to save Excel files to OneDrive (online storage) so they can be accessed anytime and anywhere.
  5. Excel Online: Access Excel files online (without the Microsoft Excel software though).
  6. Import Access Data: How to import data from Microsoft Access.
  7. Microsoft Query: How to import data from Microsoft Access using Query.
  8. Import/Export Text Files: By default, text files (such as .csv and .txt) will appear messy in Excel. Use this easiest way to tidy it up.
  9. XML: How to convert Excel files to XML data files and vice versa.

Microsoft Excel Guide Shortcut

Excel Basics
Formula/Function
Data Analytics
VBA
New Guideline
Lihat Versi Bahasa Indonesia