Microsoft Excel - Working with excel templates

Working with Microsoft Excel Templates

What is a Template?
A template is a pre-designed Excel file that contains ready-made formatting, formulas, and layouts for common tasks like invoices, budgets, or reports.

---

How to Open an Existing Template:

1. Open Excel → click File > New.
2. Choose from available templates or search online.
3. Select a template and click Create to open it.

---

How to Create a Custom Template:

1. Create or format a workbook as needed.
2. Go to File > Save As.
3. Choose Excel Template (*.xltx) from the file type list.
4. Save it in the Templates folder for future use.

---

Tip:
Next time, open Excel → File > New > Personal to reuse your custom templates.

Microsoft Excel - Excel Formula to Check if Data Exists Across Sheets (Missing or Present)

✅ Excel Formula to Check if Data Exists Across Sheets (Missing or Present)

💡 Use Case

In integration or data-tracking scenarios, you often maintain two different sheets — for example,

• one with a master list of interfaces, and
• another with a subset list you want to verify.

You need to automatically check whether each interface in your current sheet exists in the master sheet, and mark it as “Present” or “Missing.”

---

⚙️ Formula Used

=IF(COUNTIF('OIC Interfaces'!F:F, H2) = 0, "Missing", "Present")

---

🧠 Formula Breakdown

Part	Meaning
COUNTIF('OIC Interfaces'!F:F, H2)	Counts how many times the value in cell H2 appears in column F of the sheet named ‘OIC Interfaces’.
= 0	Checks if the count is zero — meaning the value doesn’t exist in that column.
"Missing"	Returned when the value is not found in the target sheet.
"Present"	Returned when the value is found in the target sheet.
IF(...)	Combines the logic to display either “Missing” or “Present.”

---

🪜 Step-by-Step Solution

1. Identify your reference sheet – for example, 'OIC Interfaces' is your master list of integrations.
2. Choose the lookup column – here, column F in 'OIC Interfaces' contains the unique identifiers or interface names.
3. Write the formula in your working sheet – in cell N2, enter the formula to check if H2 exists in the master list.
4. Copy down the formula – Excel will automatically check all rows and mark each as “Present” or “Missing.”

---

🧩 Example

Sheet 1: Working Sheet

H (Interface Name)	Result
ERP_GL_Extract	(Formula result)
EPM_SFTP_Load	(Formula result)

Sheet 2: OIC Interfaces

F (Interface Name)
EPM_SFTP_Load
HCM_Payroll_Extract

Now when you use the formula:

=IF(COUNTIF('OIC Interfaces'!F:F, H2)=0,"Missing","Present")

✅ For ERP_GL_Extract, Excel shows “Missing”
✅ For EPM_SFTP_Load, Excel shows “Present”

---

🏁 Final Takeaway

This simple IF + COUNTIF combination is one of the most practical Excel tricks for:

• Validating data consistency across multiple sheets
• Checking missing records between reports
• Comparing lists during integrations or reconciliations

It’s lightweight, dynamic, and works perfectly for ERP, OIC, or data validation reports.

Microsoft Excel - VLOOKUP Use Case

🔍  Excel VLOOKUP Use Case - Fetching Data Across Sheets

📘 Use Case

You have data stored in multiple sheets — for example, integration details in one sheet (FileBasedIntegrations_SFTPMAIL) and a list of integration names in another.
You want Excel to automatically fetch matching details (like mail ID, file path, or status) based on the integration name.

---

💡 Formula Used

=VLOOKUP(F2, FileBasedIntegrations_SFTPMAIL!A:J, 9, 0)

---

⚙️ Step-by-Step Explanation

Step	Meaning
Step 1: Identify lookup value (F2)	This is the value Excel will search for — located in cell F2 (for example, the Integration Name).
Step 2: Define table array (FileBasedIntegrations_SFTPMAIL!A:J)	This is the range where Excel will search. Here, it looks in column A of the sheet FileBasedIntegrations_SFTPMAIL and can return data from columns A through J.
Step 3: Set column index number (9)	Excel will return the value from the 9th column in the specified range, corresponding to the row where the lookup value was found.
Step 4: Specify match type (0)	The 0 (or FALSE) means exact match — Excel will only return a result if it finds an exact match for the lookup value in column A.

---

🧩 Example

Let’s say you have a lookup setup like this:

Sheet 1 (Main Sheet):

Integration Name	Details
PAYROLL_EXTRACT	(Formula here)

Sheet 2 (FileBasedIntegrations_SFTPMAIL):

A (Integration Name)	...	I (SFTP Mail ID)
PAYROLL_EXTRACT	...	hr_sftp@company.com
FINANCE_REPORT	...	finance_sftp@company.com

Now, when you enter this formula in Sheet 1:

=VLOOKUP(F2, FileBasedIntegrations_SFTPMAIL!A:J, 9, 0)

If F2 contains PAYROLL_EXTRACT, Excel searches column A in FileBasedIntegrations_SFTPMAIL, finds the matching row, and returns the value from column 9 — here, hr_sftp@company.com.

---

✅ Solution Summary

• VLOOKUP connects data across sheets using a shared key (like an ID or name).
• It saves time, reduces manual searching, and maintains consistency in large Excel reports.
• Ideal for ERP, HR, and Integration tracking spreadsheets where details are stored in separate tables.

Microsoft Excel - Printing an excel worksheet

 Working...

Viewing Your Document in Print Preview (Excel / Word)

Here’s a short guide 👇

🪟 In Microsoft Excel or Word:

1. Click the File tab.
2. Choose Print from the left menu.
3. The right side of the screen shows the Print Preview of your document.

🖱️ Shortcut:

• Press Ctrl + F2 to open Print Preview directly.

💡 Use it to:

• Check page layout, margins, headers/footers, and alignment before printing.
• Make sure your document fits properly on the page.

Changing the Margins, Scaling, and Orientation in Excel (or Word)

🧾 1. Open Print Preview

• Go to File → Print
  (or press Ctrl + F2)

---

📏 2. Change Margins

• Click Margins drop-down (below Settings).
• Choose:
  • Normal – default margin
  • Wide – larger space around content
  • Narrow – fits more data on a page
• Or select Custom Margins to set your own.

---

↕️ 3. Change Orientation

• Click Orientation drop-down.
• Choose:
  • Portrait (vertical)
  • Landscape (horizontal)

---

📐 4. Change Scaling

• Click Scaling (or No Scaling option).
• Choose:
  • Fit Sheet on One Page
  • Fit All Columns on One Page
  • Fit All Rows on One Page
  • Or set Custom Scaling Options for exact fit.

---

✅ Tip:
Use Page Layout → Page Setup group for the same options without opening Print Preview.

Working with Page Layout View in Excel 📄

🔹 What It Does

Page Layout View lets you see how your worksheet will look when printed — including margins, headers, footers, and page breaks.

---

🧭 How to Open

1. Go to the View tab.
2. In the Workbook Views group, click Page Layout View.
   (Or click the Page Layout icon at the bottom-right of the Excel window.)

---

⚙️ What You Can Do in This View

• See how data fits on each page.
• Add or edit headers and footers directly.
• Adjust margins by dragging the margin lines.
• Change page orientation (Portrait/Landscape).
• Insert page breaks visually.
• View gridlines, titles, and backgrounds as they’ll appear when printed.

---

🪄 Tip:

Switch back anytime with:

• Normal View – for regular editing.
• Page Break Preview – to adjust page breaks only.

Adding Header and Footer Content in Excel 🧾

📍 What It Does

Headers and footers appear at the top and bottom of every printed page — useful for titles, dates, or page numbers.

---

🧭 How to Add a Header or Footer

1. Go to the Insert tab.
2. Click Text → Header & Footer.
   Excel switches to Page Layout View automatically.
3. Click inside the Header or Footer area.
4. Type your text or use the Header & Footer Elements tab to insert:
   • 📅 Date
   • ⏰ Time
   • 📄 Page Number
   • 📊 Sheet Name
   • 📁 File Path or File Name

---

🧩 Quick Shortcuts

• &[Date] → Inserts current date
• &[Page] → Inserts page number
• &[File] → Inserts file name

---

🔄 Exit Header/Footer Mode

Click anywhere outside the header or footer area or switch back to Normal View (View tab → Normal).

---

Printing a Specific Range of Cells in Excel 🖨️

📏 To Print Only Selected Cells:

1. Select the range of cells you want to print.
2. Go to the Page Layout tab → Print Area → Set Print Area.
   • Excel marks this selection as the printable area.
3. To check it: Go to File → Print (or press Ctrl + F2)
   → You’ll see only that selected range in Print Preview.

---

🧭 To Clear or Change the Print Area:

• Page Layout → Print Area → Clear Print Area
  (then you can set a new one).

---

💡 Tip:

If you often print the same range, save the file — Excel remembers the print area for next time.

Microsoft Excel - Creating basic charts in excel

📊 Creating a Column Chart in Excel

1. Select the data range you want to chart.
2. Go to Insert → Column or Bar Chart.
3. Choose a Column Chart style (Clustered, Stacked, etc.).
4. Use Chart Design and Format tabs to customize title, colors, and layout.

🎨 Working with the Excel Chart Ribbon

1. Click your chart — two tabs appear: Chart Design and Format.
2. Chart Design: change chart type, layout, style, switch rows/columns, select data.
3. Format: adjust colors, shapes, text, and chart elements’ styles.

✏️ Adding & Modifying Data on an Excel Chart

1. Click the chart → go to Chart Design → Select Data.
2. Click Add, Edit, or Remove to change data series.
3. Drag blue or green borders in the worksheet to adjust the data range.
4. Chart updates automatically with new or changed data.

Note: Select 1 row or rows and then press CTRL + select rows again which you want to show.

🎨 Formatting an Excel Chart

1. Click the chart to show Chart Design and Format tabs.
2. Use Chart Design to change style, layout, or colors.
3. Use Format to edit fonts, shapes, borders, and backgrounds.
4. Right-click elements (title, axis, legend) → choose Format for detailed options.

📂 Moving a Chart to Another Worksheet

1. Click the chart.
2. Go to Chart Design → Move Chart.
3. Choose New Sheet or Object in (select worksheet).
4. Click OK — the chart moves to the chosen location.

🥧 Working with Excel Pie Charts

1. Select data (labels + values).
2. Insert → Pie Chart → choose style (2-D, 3-D, Doughnut).
3. Use Chart Design to change layout or style.
4. Use Format to adjust colors, borders, and text.
5. Add Data Labels for values or percentages.

Microsoft Excel - Inserting shapes and images into an excel worksheet

🖼️ Inserting Images in Excel 

1. Go to Insert tab.
2. Click Pictures → choose This Device, Stock Images, or Online Pictures.
3. Select the image → click Insert.

✅ Tip: Resize or move the image as needed.

➕ Insert & Format Shapes in Excel

1. Insert tab → Shapes → pick a shape.
2. Draw on sheet.
3. Use Shape Format tab to change color, outline, or effects.

💡 Working with SmartArt in Excel

1. Go to Insert → SmartArt.
2. Choose a category (List, Process, Cycle, etc.) → click OK.
3. A SmartArt graphic appears — type text directly in boxes or use the Text Pane.
4. Use SmartArt Design tab to change layout or color.
5. Use Format tab to style shapes, text, or effects.

Microsoft Excel - Formatting data in an excel workbook

Working with Font Formatting Commands 🎨 Font Formatting Commands Overview

Font formatting lets you change the appearance of text — making data more readable and visually appealing.

---

🧱 Common Font Formatting Commands

Command	Description	Shortcut
Bold (B)	Makes text darker and thicker	Ctrl + B
Italic (I)	Slants text to the right	Ctrl + I
Underline (U)	Adds a line below text	Ctrl + U
Font Type	Changes the style (e.g., Arial, Calibri, Times New Roman)	—
Font Size	Changes the size of the text (e.g., 10, 12, 14 pt)	—
Font Color	Changes text color	—
Fill Color	Adds background color to cells	—
Strikethrough	Draws a line through text	Ctrl + 5
Increase/Decrease Font Size	Makes text larger or smaller	Ctrl + Shift + > / <

---

🧰 How to Use Font Formatting Commands

1. Select the cell(s) or text you want to format.
2. Go to the Home tab → Font group.
3. Click the desired command — Bold, Font Color, Size, etc.
4. Alternatively, use keyboard shortcuts for quicker access.

🎨 Change Background Color in Excel

1. Select the cell(s).
2. Go to Home → Fill Color (🪣).
3. Pick a color.

Shortcut: Alt + H + H
Tip: Use Font Color (A) to change text color.

➿ Add Borders in Excel

1. Select the cell(s).
2. Go to Home → Borders (▢).
3. Choose a border style.

Shortcut: Alt + H + B

💰 Format as Currency

1. Select the cell(s).
2. Go to Home → Number Format → Currency.

Shortcut: Ctrl + Shift + $

📊 Format as Percentage

1. Select the cell(s).

2. Go to Home → Number Format → Percentage.

Shortcut: Ctrl + Shift + %

Increase/Decrease Decimal in Excel

• Increase Decimal: Adds more digits after decimal.
• Decrease Decimal: Removes digits after decimal.

Example:

• Cell A1 = 45.6
• Increase Decimal → 45.60 → 45.600
• Decrease Decimal → 45.60 → 45.6

Excel Format Painter:

Quickly copy formatting (font, color, borders, number format, alignment) from one cell/range to another without changing the content.

1. Select cell with desired format.
2. Click Format Painter.
3. Click/drag on target cells.
4. Double-click for multiple uses; press Esc to stop.

Merge & Center in Excel

• Purpose: Combines selected cells into one and centers the content.
• How to:
  1. Select cells to merge.
  2. Home → Merge & Center.
• Example:
  • Cells A1:C1 contain “Sales” → Merge & Center → single cell A1 shows Sales centered.

Creating Styles in Excel

• Purpose: Quickly apply a set of formats (font, color, borders, number format) to cells.

• How to:

  1. Home → Cell Styles → New Cell Style.
  2. Name the style and choose formats.
  3. Apply by selecting cell(s) → click your style.

• Example:

• Create a style named “Highlight” → yellow fill + bold text → apply to important data cells.

Using Conditional Formatting in Excel

• Purpose: Automatically format cells based on rules/conditions (e.g., highlight values, change colors).

• How to:

  1. Select cells.
  2. Home → Conditional Formatting.
  3. Choose a rule type (e.g., Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales).
  4. Set condition and format.

• Example:

• Cells A1:A10 → Highlight >50 → fill color red.

Editing Conditional Formatting in Excel

• How to:

  1. Select the cells with conditional formatting.
  2. Home → Conditional Formatting → Manage Rules.
  3. Select the rule → Edit Rule to change condition or format.
  4. Click OK to save changes.

• Example:

  • Original rule: Highlight cells >50 in red.
  • Edit rule → change to >75 → now only values >75 are highlighted.