Intermediate Level

Creating a Pivot Table from Multiple Ranges or Tables in Excel. Also discuss the default
aggregation operation in Pivot Table.

To create a Pivot Table from multiple ranges or tables, follow these steps:
1. Consolidate data using Power Query or manually create relationships between the tables.
2. Go to Insert → Pivot Table.
3. Choose Use an External Data Source and select the tables you want to combine.
4. Once tables are added, you can use the fields from each table in your Pivot Table.
Alternatively, you can use the Data Model in Excel by: - Loading each table into the Data Model (using Power Query). - Establish relationships between the tables based on common fields. - Use the fields from multiple tables in a single Pivot Table.
Default Aggregation Operations in Excel.
SUM(), MIN(), MAX(), COUNT(), AVG()

Using RAND and RANDBETWEEN Functions to Generate Random Numbers for Simulations.

- RAND generates a random decimal number between 0 and 1. - RANDBETWEEN generates a random integer between two specified numbers.
Example:
- RAND Example:
=RAND()
This might return a value like 0.4567.
- RANDBETWEEN Example:
=RANDBETWEEN(10, 50) - This will return a random integer between 10 and 50, for example, 37.
You can combine these functions with other formulas to create more complex simulations. For
instance, if you wanted to simulate random sales figures:
=RANDBETWEEN(1000, 5000) * RAND()
This would return a random sales figure between 1000 and 5000, with decimal precision.

Using the TRANSPOSE Function to Switch Rows to Columns and Vice Versa

The TRANSPOSE function is used to switch rows to columns or columns to rows.
Example:
Assume you have data in range A1:C1 (row): 1, 2, 3.
To switch this row to a column:
1. Select an empty range of cells, say A2:A4.
2. Enter the formula:
=TRANSPOSE(A1:C1)
3. Press Ctrl + Shift + Enter to apply this as an array formula (in older versions of Excel). In newer
versions (Excel 365), just pressing Enter is enough.
The result will be:
1
2
3
If you had data in a column, you could similarly transpose it into a row by selecting an empty row
range and using the same formula.