📊 Microsoft Excel Basics: Complete Guide English

🎯 Why Learn Excel?

Excel is the most popular data analysis tool in the workplace. 82% of office workers use Excel!

  • 💰 Finance
    Budget, invoices, financial reports
  • 📈 Data Analysis
    Statistics, graphs, dashboards
  • 📋 Administration
    Inventory lists, schedules
  • 🎯 Project Management
    Timeline, progress tracking

📑 Excel Workbook Structure

  1. Workbook
    Excel File (.xlsx)
  2. Worksheet
    Sheet in a workbook
  3. Cell
    Row & column intersection
  4. Formula Bar
    A place to write formulas

📍 Excel Coordinate System

     A     B     C     D
  1 |  A1 |  B1 |  C1 |  D1 |
  2 |  A2 |  B2 |  C2 |  D2 |
  3 |  A3 |  B3 |  C3 |  D3 |
  4 |  A4 |  B4 |  C4 |  D4 |

  Cell Reference: Column Letter + Row Number
  Example: B3 = Column B, Row 3

🔢 10 Most Important Excel Formulas

Formula Function Example Result
=SUM() Add numbers =SUM(A1:A5) Total A1 to A5
=AVERAGE() Average =AVERAGE(B2:B10) Average B2-B10
=MAX() Highest value =MAX(C1:C20) Max value C1-C20
=MIN() Lowest value =MIN(D5:D15) Min value D5-D15
=COUNT() Count cells with numbers =COUNT(E1:E100) Number count
=IF() Conditional logic =IF(F1>50,"Pass","Fail") Check condition
=VLOOKUP() Vertical data lookup =VLOOKUP(G1,A:B,2,FALSE) Lookup value
=CONCATENATE() Combine text =CONCATENATE(H1," ",H2) Combine text
=TODAY() Today's date =TODAY() 15/01/2024
=ROUND() Rounding =ROUND(I1,2) Round to 2 decimals

📈 Create Simple Charts

Sample Sales Data:

Month Product A Product B Total
January 150 200 350
February 180 220 400
March 210 250 460

Steps to Create Chart:

  1. Select data (A1:D4)
  2. Insert → Recommended Charts
  3. Choose Column Chart
  4. Customize title & colors
  5. Place chart in worksheet

Tip: Use Ctrl + T to convert to Table for automatic chart updates!

🔢 Data Formatting

💰 Currency
Rp 1,500,000
Ctrl+Shift+4		 📅 Date
15 January 2024
Ctrl+Shift+3
📊 Percentage
75.50%
Ctrl+Shift+5		 🔢 Number
1,234.56
Ctrl+Shift+1

⚡ Important Excel Shortcuts

Shortcut Function Category
Ctrl + S Save workbook 💾 Basic
Ctrl + C / V Copy / Paste 📋 Editing
Ctrl + Z / Y Undo / Redo ↪️ Editing
Ctrl + Arrow Key Navigate to edge 📍 Navigation
Ctrl + Shift + L Toggle Filter 🔍 Data
Alt + = AutoSum 🧮 Formula
F2 Edit cell ✏️ Editing
Ctrl + T Create Table 📊 Formatting

📋 Project: Create Personal Budget

Task: Create Personal Financial Report

Create spreadsheet with this structure:

  PERSONAL BUDGET 2024
  ├── 📄 Sheet 1: INCOME
  │   ├── Basic Salary
  │   ├── Freelance
  │   ├── Bonus
  │   └── TOTAL INCOME (SUM formula)
  │
  ├── 📄 Sheet 2: EXPENSES
  │   ├── Needs (food, transport)
  │   ├── Bills (electricity, internet)
  │   ├── Entertainment
  │   ├── Savings
  │   └── TOTAL EXPENSES (SUM formula)
  │
  └── 📄 Sheet 3: SUMMARY
      ├── Monthly Balance
      ├── Income vs Expenses Chart
      ├── Savings Percentage
      └── CONCLUSION (IF formula: "Healthy"/"Needs Adjustment")

Requirements:

  • Use =SUM() for totals
  • Use =IF() for conclusion
  • Create pie chart for expenses
  • Format currency for all numbers
  • Use cell styles for headers

🔍 Filter & Sort Data

Sample Employee Data:

Name Department Salary Status
Andy Marketing 8,000,000 Active
Budi IT 12,000,000 Active

How to Filter:

  1. Select data range
  2. Data → Filter (Ctrl+Shift+L)
  3. Click dropdown in header
  4. Select criteria (e.g., Salary > 10,000,000)
  5. Apply filter

How to Sort:

  1. Select column to sort
  2. Data → Sort A to Z (ascending)
  3. Or Sort Z to A (descending)

⚠️ Common Errors & Solutions

❌ Error ✅ Solution
#VALUE! error Check data type (text vs number)
#REF! error Invalid cell reference
File too large Delete unused cells, compress images
Formula not updating Calculation Options → Automatic
Data not filtering Ensure no blank rows in range

🚀 Advanced Level: Basic Pivot Tables

📊 Magic of Pivot Tables

Pivot Tables are the most powerful data analysis tool in Excel!

How to Create Pivot Table:

  1. Select data range
  2. Insert → PivotTable
  3. Drag fields to areas:
    • Rows: Category (e.g., Month)
    • Columns: Sub-category (e.g., Product)
    • Values: Numerical data (e.g., Sales)
    • Filters: Global filter
  4. Customize layout & design

Example Use Case: Analyze sales per region per month!

📈 Excel Roadmap: 30 Day Mastery

Week 1
Basic Functions		 Week 3
Data Analysis
Week 2
Formulas & Charts		 Week 4
Pivot & Advanced
Bonus
Macros & VBA

Commitment: 1 hour/day of practice = Excel Expert in 1 month!

📊 Other Excel Topics 💼 Microsoft Office

🎯 Se sidauk komprende sa aprende Excel?

Excel maka ferramenta análize dadus ne'ebé popular liu iha mundu serbisu. 82% husi traballadór eskritóriu sira uza Excel!

  • 💰 Finansas
    Orsamentu, fatura, relatóriu finanseiru
  • 📈 Análize Dadus
    Estatístika, gráfiku, dashboard
  • 📋 Administrasaun
    Lista inventáriu, horáriu
  • 🎯 Jestun Projetu
    Linha tempu, monitorizasaun progressu

📑 Estrutura Workbook Excel

  1. Workbook
    Ficheiru Excel (.xlsx)
  2. Worksheet
    Folha iha dentru workbook
  3. Cell
    Kotu enkontu entre linha & koluna
  4. Formula Bar
    Lugar hodi hakerek fórmula

📍 Sistema Koordenada Excel

     A     B     C     D
  1 |  A1 |  B1 |  C1 |  D1 |
  2 |  A2 |  B2 |  C2 |  D2 |
  3 |  A3 |  B3 |  C3 |  D3 |
  4 |  A4 |  B4 |  C4 |  D4 |

  Referénsia Cell: Letra Koluna + Númeru Linha
  Exemplu: B3 = Koluna B, Linha 3

🔢 10 Fórmula Excel Importante Liu

Fórmula Funsaun Exemplu Rezultadu
=SUM() Soma númeru =SUM(A1:A5) Total A1 to'o A5
=AVERAGE() Média =AVERAGE(B2:B10) Média B2-B10
=MAX() Valór altu =MAX(C1:C20) Valór máksimu C1-C20
=MIN() Valór baixu =MIN(D5:D15) Valór mínimu D5-D15
=COUNT() Konta selula ho númeru =COUNT(E1:E100) Kontagem númeru
=IF() Lójika kondisionál =IF(F1>50,"Lae","Falha") Verifika kondisaun
=VLOOKUP() Prokura dadus vertikál =VLOOKUP(G1,A:B,2,FALSE) Prokura valór
=CONCATENATE() Kombina textu =CONCATENATE(H1," ",H2) Kombina textu
=TODAY() Data oin =TODAY() 15/01/2024
=ROUND() Arredondamentu =ROUND(I1,2) Arredonda ba 2 desimál

📈 Kria Gráfiku Simples

Dadu Amostra Venda:

Fulan Produtu A Produtu B Total
Janeiru 150 200 350
Fevereiru 180 220 400
Marsu 210 250 460

Passu hodi Kria Chart:

  1. Seleciona dadus (A1:D4)
  2. Insert → Gráfiku Rekomendadu
  3. Escolhe Gráfiku Koluna
  4. Personaliza títulu & kór
  5. Coloka gráfiku iha worksheet

Dika: Uza Ctrl + T hodi konverte ba Tabela ba gráfiku atualiza otomatikamente!

🔢 Formata Dadus

💰 Moeda
Rp 1.500.000
Ctrl+Shift+4		 📅 Data
15 Janeiru 2024
Ctrl+Shift+3
📊 Persentuajen
75,50%
Ctrl+Shift+5		 🔢 Númeru
1.234,56
Ctrl+Shift+1

⚡ Atalhu Importante Excel

Atalhu Funsaun Kategoria
Ctrl + S Grava workbook 💾 Báziku
Ctrl + C / V Kópia / Kola 📋 Edisaun
Ctrl + Z / Y Desfazer / Refazer ↪️ Edisaun
Ctrl + Tekla Frecha Navega ba extremidade 📍 Navegasaun
Ctrl + Shift + L Ativa/Desativa Filtu 🔍 Dadus
Alt + = Soma Automátika 🧮 Fórmula
F2 Edita selula ✏️ Edisaun
Ctrl + T Kria Tabela 📊 Formataun

📋 Projetu: Kria Orsamentu Pessuál

Tarefa: Kria Relatóriu Finanseiru Pessuál

Kria spreadsheet ho estrutura tuir mai:

  ORSAMENTU PESSUÁL 2024
  ├── 📄 Folha 1: RECEITA
  │   ├── Saláriu Báziku
  │   ├── Freelance
  │   ├── Bónus
  │   └── TOTAL RECEITA (fórmula SUM)
  │
  ├── 📄 Folha 2: DESPEZA
  │   ├── Nesesidade (han, transporte)
  │   ├── Konta (eletrisidade, internet)
  │   ├── Lazer
  │   ├── Poupansa
  │   └── TOTAL DESPEZA (fórmula SUM)
  │
  └── 📄 Folha 3: RESUMU
      ├── Saldu Mensál
      ├── Gráfiku Receita vs Despeza
      ├── Persentuajen Poupansa
      └── KONKLUZÁUN (fórmula IF: "Saude"/"Korrexe")

Requisitus:

  • Uza =SUM() ba total
  • Uza =IF() ba konkluzaun
  • Kria gráfiku tipu pizza ba despeza
  • Formata moeda ba númeru hotu
  • Uza estilu selula ba kabeça

🔍 Filtu & Ordena Dadus

Dadu Amostra Empregadu:

Naran Departamentu Saláriu Status
Andi Marketing 8.000.000 Ativu
Budi IT 12.000.000 Ativu

Modu Filtu:

  1. Seleciona intervalu dadus
  2. Dadus → Filtu (Ctrl+Shift+L)
  3. Klika menu desizente iha kabeça
  4. Seleciona kritéru (exemplu: Saláriu > 10.000.000)
  5. Aplika filtu

Modu Ordena:

  1. Seleciona koluna hodi ordena
  2. Dadus → Ordena A to Z (asendente)
  3. Ou Ordena Z to A (desendente)

⚠️ Erros Komuns & Solusaun

❌ Erro ✅ Solusaun
#VALUE! error Verifika tipu dadus (textu vs númeru)
#REF! error Referénsia selula inválida
Ficheiru too grande Apaga selula la uza, komprime imagem
Fórmula la atualiza Opasaun Kalkulasaun → Automátiku
Dadus la hetan filtu Garante ne'ebé la iha liña branku iha intervalu

🚀 Nivel Superior: Tabela Dinámika Báziku

📊 Májika Tabelas Dinámika

Tabelas Dinámica (PivotTables) maka ferramenta análize dadus boot poderuza iha Excel!

Modu Kria Tabela Dinámica:

  1. Seleciona intervalu dadus
  2. Insert → Tabela Dinâmica
  3. Arrasta kaampu ba área:
    • Linha: Kategoria (exemplu: Fulan)
    • Koluna: Sub-kategoria (exemplu: Produtu)
    • Valór: Dadus numéruku (exemplu: Venda)
    • Filtu: Filtu globál
  4. Personaliza layout & designu

Ezemplu Kazu Uza: Análize venda por rejião kada fulan!

📈 Mapa Estrada Excel: 30 Dias Dominu

Semana 1
Funsaun Báziku		 Semana 3
Análize Dadus
Semana 2
Fórmulas & Gráfiku		 Semana 4
Tabela Dinámica & Avansadu
Bónus
Makros & VBA

Kompromissu: 1 oras/dia de prática = Espértu Excel iha 1 fulan!

📊 Tópiku Excel Seluk 💼 Microsoft Office

Next →