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.

OIC - Cash Actual and Forecast Cost Data Transfer from ERP to GTMS

Working...

🧩 Use Case: Cash Actual and Forecast Cost Data Transfer from ERP to GTMS

🎯 Objective

Automate extraction of Cash Actual and Forecast Cost data from ERP CM (Cash Management) using ESS → BIP flow, and deliver transformed files to GTMS shared folder, followed by a trigger file.

Here we will learn the following key concepts in Oracle Integration Cloud (OIC):

1. Calling an ESS Job (ESS → BIP) from OIC – Learn how to trigger a report or process in ERP Cloud by invoking an ESS job that generates a BIP report with required parameters.

2. Subscribing to an Event – Understand how to configure OIC to listen for ERP bulk export events (triggered when the ESS job completes successfully) and automatically start the next integration flow.

3. Downloading File from ESS Job – Learn how to retrieve the BIP output file from ERP Cloud once the job completes, using the ERP adapter or REST API.

4. Updating Lookup for Sequence Number – Explore how to use an OIC lookup table to maintain and update a unique sequence number, which is later used in the output file name and trigger file.

5. Trigger File Concept – Understand the purpose of a trigger file, a small control file (e.g., .trg or .txt) sent along with the main data file to the target system (like GTMS) to signal that the main file is ready for processing.

---

⚙️ Solution Overview

The integration flow is divided into two main schedulers and one event-driven main service.

---

1️⃣ Scheduler Integration – Cash Actual Extract

Name: SCHED_CashActual_Extract

Purpose:
Trigger ERP ESS job to generate the Cash Actual Report via BIP with parameters.

Steps:

1. Read From Date and To Date parameters (scheduler inputs).
2. Invoke ERP Cloud Adapter →ErpIntegrationService >> exportBulkData operation (for Cash Actual BIP report).
3. Set parameters like report name, date range.
1. JobOptions = "EnableEvent = Y" in ESS payload for bulk export (so ERP emits event once report is ready)
2. Job name= jobpackagename,jobdefinitionname
3. Parameterlist: concat($FromDate,",",$ToDate)

---

2️⃣ Scheduler Integration – Forecast Cost Extract

Name: SCHED_ForecastCost_Extract

Purpose:
Same as above, but for Forecast Cost BIP report.

Steps:

1. Read From Date and To Date parameters.
2. Submit ESS job for Forecast Cost BIP report (with EnableEvent = Y).
3. ERP emits event on report completion.
4. Store metadata if required.

ESS job creation:

My enterprise >> setup and maintenance >> search tasks >> Manage Enterprise Scheduler >> click manage Enterprise Scheduler Job Definttions and job sets for financial, supply chain management and related .. >>create >>

Create BIP reports:

Tools >> Reports and Analytics >> Browse Catalog >> create Data Model and Report 

---

3️⃣ Event-Driven Main Service – ERP Event Handler

Name: EVT_ERP_BulkExport_Handler

Trigger:
ERP Bulk Export Event (emitted when ESS job completes with event-enabled flag).

Purpose:
Process the exported BIP file, transform, send to GTMS folder, generate trigger file, and update lookup.

Steps:

1. Subscribe to ERP event (ERP → OIC via ERP Cloud Adapter).
2. Download file from ERP content repository (using ERP REST API or File Download endpoint).
3. Get sequence number from OIC Lookup (e.g., SEQ_LOOKUP).
   • This number is used in both output filename and trigger file content.
4. Transform ERP report data to GTMS file structure (using XSLT or Stage File mapping).
5. Write output file to GTMS shared folder (via FTP/SFTP adapter).
   • Example filename:

     CashForecast_<SeqNum>_<YYYYMMDD>.csv
     

6. Generate trigger file (e.g., .TRG or .TXT) with content containing sequence number or status.
   • Example content:

     FILE_NAME=CashForecast_102_20251022.csv
     STATUS=SUCCESS
     

7. Upload trigger file to the same GTMS folder.
8. Update lookup (increment sequence number) to mark completion.
9. Send optional notification or audit log update.

---

🧱 Components Used

Component	Type	Description
Scheduler	Integration	Triggers ESS jobs for Cash Actual & Forecast
ERP Adapter	Connection	Used for ESS job submission and file download
Lookup	Configuration	Stores and updates sequence number
FTP/SFTP Adapter	Connection	For GTMS shared folder file delivery
Event Subscription	Mechanism	ERP Bulk Export event triggers main flow
XSLT Mapping	Transformation	To convert ERP BIP file to GTMS expected format

---

🪜 End-to-End Flow Summary

1. Scheduler 1 & 2 trigger ESS jobs for each report (Cash Actual & Forecast Cost).
2. ESS jobs generate BIP reports and emit events (eventEnabled=Y).
3. Main Event Integration is triggered → downloads BIP output.
4. Transforms and uploads the data file to GTMS shared folder.
5. Retrieves sequence from lookup, updates it post success.
6. Creates trigger file, uploads it to the GTMS folder.
7. End.

---

🧭 High-Level Architecture Diagram

             +--------------------+
             |  Scheduler 1       |
             | (Cash Actual)      |
             +---------+----------+
                       |
                       | Submit ESS Job (BIP)
                       v
             +--------------------+
             |  Oracle ERP Cloud  |
             |  (ESS + BIP Report)|
             +---------+----------+
                       |
                       | Event (Bulk Export)
                       v
             +-----------------------------+
             |  OIC Main Event Integration |
             |  (EVT_ERP_BulkExport_Handler)|
             +---------+----------+---------+
                       |          |
               Download File     Lookup Sequence
                       |          |
                       v          v
             +----------------------------+
             |  Transform (XSLT Mapping)  |
             +-------------+--------------+
                           |
                 Upload to GTMS Folder
                           |
                           v
             +----------------------------+
             | Generate & Send Trigger File|
             +-------------+--------------+
                           |
                     Update Lookup
                           |
                           v
                    Integration Success

---

✅ Error Handling & Retry Strategy

• ESS job failure: Capture and notify via email/log.
• File download error: Retry with exponential delay (3 attempts).
• Lookup update failure: Rollback or queue update post success.
• GTMS upload failure: Use stage file retry or fault handler to reattempt.

Microsoft Excel - Modifying an excel worksheet

Hiding and Unhiding Excel Rows and Columns

Hiding Rows/Columns: Select them → Right-click → Hide.
Unhiding Rows/Columns: Select nearby rows/columns → Right-click → Unhide.

Renaming an excel sheet

1. Double-click the sheet tab → Type new name → Press Enter.
2. Right-click the sheet tab → Select Rename → Type new name → Press Enter.

Note: Similarly we can delete the sheet.

Changing the Width and Height of Cells

Drag row/column border or Right-click → Row Height / Column Width → Enter value.

Moving and Copying an Excel Worksheet

Move: Drag sheet tab to new spot.
Copy: Hold Ctrl and drag sheet tab.

Move or copy an excel sheet from one to another workbook

Right-click sheet tab → Move or Copy → Choose workbook → Check Create a copy (if needed) → Click OK.

Inserting and Deleting Rows and Columns

Insert:

• Use Ctrl + + (plus).
• Or Right-click → Insert.

Delete:

• Use Ctrl + – (minus).
• Or Right-click → Delete.

Tip: Ctrl + Shift + + inserts entire row/column.

Microsoft Excel - working with basic Excel Functions

Excel Function

A function in Excel is a predefined formula that performs a specific calculation using the values provided to it.
Functions help simplify complex calculations and save time.

---

Building Block of an Excel Function

Each Excel function has three main parts:

1. Equal Sign (=) – Indicates that a formula or function is being entered.
2. Function Name – The name of the specific operation (e.g., SUM, AVERAGE, MAX).
3. Arguments – The values or cell references enclosed in parentheses that the function uses to perform calculations.

📘 Syntax:
=FunctionName(Arguments)

📊 Example:
=SUM(B4:B8) → Adds all the values from cells B4 to B8.

---

Function Argument Window 

The Function Argument Window is a dialog box in Excel that helps users enter and understand the arguments required for a function.
It appears when you click the “fx” button next to the formula bar or start typing a function.

This window displays:

• The name of the function and a short description of what it does.
• Input fields for each argument, showing whether they are required or optional.
• A preview or result section at the bottom that shows the outcome of the calculation as you enter the data.

💡 It is especially helpful for beginners to correctly structure functions without remembering the exact syntax.

🧭 Formula Tab – Function Library

The Formula Tab in Excel is used to create, edit, and manage formulas and functions easily.
Within this tab, the Function Library group provides a quick way to insert various built-in Excel functions categorized by type.

It helps users find and apply formulas without remembering their exact names or syntax.

---

🗂️ Main Sections in the Function Library

1. AutoSum

• Automatically adds a range of numbers.
• Click the AutoSum (Σ) button to quickly insert functions like:
  • SUM – Adds numbers.
  • AVERAGE – Finds the average.
  • COUNT – Counts numeric cells.
  • MAX – Finds the highest value.
  • MIN – Finds the lowest value.

📘 Shortcut: Select a range and press Alt + = to insert SUM automatically.

2. Recently Used

• Shows a list of functions you have used recently.
• Makes it easy to reuse functions without searching again.

3. Financial

• Contains functions used for financial calculations.
  • Examples: PMT, FV, PV, RATE, NPV.

4. Logical

• Includes functions used for decision-making and condition checking.
  • Examples: IF, AND, OR, NOT.

5. Text

• Functions for handling text and strings.
  • Examples: LEFT, RIGHT, MID, LEN, UPPER, LOWER, CONCAT.

6. Date & Time

• Used to insert and calculate date and time values.
  • Examples: TODAY, NOW, DAY, MONTH, YEAR, DATEDIF.

7. Lookup & Reference

• Functions used to search for and retrieve data from tables or ranges.
  • Examples: VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE.

8. Math & Trig

• Contains mathematical and trigonometric functions.
  • Examples: SUM, ROUND, PRODUCT, POWER, SQRT, ABS.

9. More Functions

• Includes specialized function categories:
  • Statistical: AVERAGEIF, COUNTIF, MEDIAN, MODE
  • Engineering: CONVERT, COMPLEX
  • Information: ISERROR, ISBLANK
  • Compatibility: Older Excel functions for backward compatibility.

💡 Extra Option: Insert Function (fx)

• Located at the left of the formula bar and also in the Function Library.
• Opens the Function Arguments window, helping users insert functions step-by-step.
• Provides descriptions, required arguments, and a preview of results.

🧮 How to Use a Function from the Formula Tab (Example: SUM Function)

Step 1: Open your Excel sheet

Make sure the worksheet has some numeric data (for example, numbers in cells B2 to B6).

Step 2: Select the cell for the result

Click on the cell where you want the total (for example, B7).

Step 3: Go to the Formula Tab

On the Excel ribbon, click the Formulas tab.
You’ll see several groups such as Function Library, Defined Names, Formula Auditing, etc.

Step 4: Choose Math & Trigonometry

In the Function Library group, click on Math & Trig.
A dropdown list of math-related functions will appear.

Step 5: Select the SUM function

Scroll and click on SUM from the list.
The Function Arguments window will open.

Step 6: Enter the cell range

In the Function Arguments window:

• In the Number1 box, enter the range of cells you want to add (for example, B2:B6).
• You can also select the range directly on your worksheet using the mouse.

Step 7: Preview the result

Excel will instantly show the calculated result at the bottom of the Function Arguments window, so you can confirm it’s correct.

Step 8: Click OK

After confirming the range and result, click OK.
The total sum will appear in the selected cell (B7).

---

📘 Final Formula Example

=SUM(B2:B6)

This adds all numbers from cells B2 to B6 and displays the total in B7.

🔎 Using fx (Insert Function) Option

1. Click the fx button beside the formula bar.
2. In the Insert Function box, type MIN and press Go.
3. Select MIN from the list → click OK.
4. Enter the cell range (e.g., B2:B6).
5. Click OK → the smallest value appears in the selected cell.

📘 Example: =MIN(B2:B6)




We can also use MAX(), AVERAGE() AND COUNT() etc. functions.

Adjacent cells error in excel calculation

➕ Using AutoSum Command

1. Select the cell for the result.
2. Click AutoSum (Σ) on the Home or Formulas tab.
3. Excel auto-selects nearby cells — press Enter to confirm.

📘 Example: =SUM(B2:B6)




Note: we can also use shortcut key: Alt + = to perform autosum.

🔁 Using AutoFill to Copy Formulas

1. Select the cell with the formula.
2. Drag the fill handle (small square at the corner) across cells.
3. Excel copies and adjusts the formula automatically.