# data[].attributes.headline
Sect 1-Fruit Sales Exercise Latest (PIVOT)

# data[].attributes.subject
Sect 1-Fruit Sales Exercise Latest (PIVOT)

# data[].attributes.attachment_link.title
Fruit_Sales_Data with pivot table (sect 1) latest

# data[].attributes.headline
Sect 1- Pivot Exercise (Supermarket Sales)

# data[].attributes.subject
Sect 1- Pivot Exercise (Supermarket Sales)

# data[].attributes.headline
Chapter 1 -intro to data analytics(pdf version)

# data[].attributes.subject
Chapter 1 -intro to data analytics(pdf version)

# data[].attributes.body
credit to microsoft : https://powerbi.microsoft.com/en-us/

# data[].attributes.attachment_link.readable_text
Insert a Pivot Table | Drag fields | Sort | Filter | Change Summary Calculation | Two-dimensional Pivot Table

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.

Insert a Pivot Table

To insert a pivot table, execute the following steps.

1. Click any single cell inside the data set.

2. On the Insert tab, in the Tables group, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.

3. Click OK.

Drag fields

The PivotTable Fields pane appears. To get the total amount exported of each product, drag the following fields to the different areas.

1. Product field to the Rows area.

2. Amount field to the Values area.

3. Country field to the Filters area.

Below you can find the pivot table. Bananas are our main export product. That's how easy pivot tables can be!

Sort

To get Banana at the top of the list, sort the pivot table.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Sort, Sort Largest to Smallest.

Result.

Filter

Because we added the Country field to the Filters area, we can filter this pivot table by Country. For example, which products do we export the most to France?

1. Click the filter drop-down and select France.

Result. Apples are our main export product to France.

Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts of specific products.

Change Summary Calculation

By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Value Field Settings.

3. Choose the type of calculation you want to use. For example, click Count.

4. Click OK.

Result. 16 out of the 28 orders to France were 'Apple' orders.

Two-dimensional Pivot Table

If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot table. First, insert a pivot table. Next, to get the total amount exported to each country, of each product, drag the following fields to the different areas.

1. Country field to the Rows area.

2. Product field to the Columns area.

3. Amount field to the Values area.

4. Category field to the Filters area.

Below you can find the two-dimensional pivot table.

To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.

# data[].attributes.attachment_link.description
Insert a Pivot Table | Drag fields | Sort | Filter | Change Summary Calculation | Two-dimensional Pivot Table Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. Our data set consists of 213 records and 6 fields.

# data[].attributes.headline
Sample Report for Assignment 1 - Edited Version

# data[].attributes.subject
Sample Report for Assignment 1 - Edited Version

# data[].attributes.body
Hi everyone..Assalamualaikum & greetings everyone.  I am Mdm. Jihadah Ahmad your lecturer.This is an additional online platform for Intro To Data Analytics class activities.  Our main platform will still be the UPTM LMS. I shall update the content weekly according to our class progress. I do hope you enjoy the class & best wishes

# data[].attributes.attachment_link.description
Pivot tables in excel✔️ summarize the data of a more extensive table. Read this article to ✔️learn how to create a pivot table in excel, and apply filters.

# data[].attributes.attachment_link.readable_text
In this Microsoft Excel tutorial, we will learn the Microsoft Exel basics. These Microsoft Excel notes will help you learn every MS Excel concepts. Let's start with the introduction:

What is Microsoft Excel?

Microsoft Excel is a spreadsheet program used to record and analyze numerical and statistical data. Microsoft Excel provides multiple features to perform various operations like calculations, pivot tables, graph tools, macro programming, etc. It is compatible with multiple OS like Windows, macOS, Android and iOS.

A Excel spreadsheet can be understood as a collection of columns and rows that form a table. Alphabetical letters are usually assigned to columns, and numbers are usually assigned to rows. The point where a column and a row meet is called a cell. The address of a cell is given by the letter representing the column and the number representing a row.

Why Should I Learn Microsoft Excel?

We all deal with numbers in one way or the other. We all have daily expenses which we pay for from the monthly income that we earn. For one to spend wisely, they will need to know their income vs. expenditure. Microsoft Excel comes in handy when we want to record, analyze and store such numeric data. Let's illustrate this using the following image.

Where can I get Microsoft Excel?

There are number of ways in which you can get Microsoft Excel. You can buy it from a hardware computer shop that also sells software. Microsoft Excel is part of the Microsoft Office suite of programs. Alternatively, you can download it from the Microsoft website but you will have to buy the license key.

In this Microsoft Excel tutorial, we are going to cover the following topics about MS Excel.

How to Open Microsoft Excel?

Running Excel is not different from running any other Windows program. If you are running Windows with a GUI like (Windows XP, Vista, and 7) follow the following steps.

Click on start menu

Point to all programs

Point to Microsoft Excel

Click on Microsoft Excel

Alternatively, you can also open it from the start menu if it has been added there. You can also open it from the desktop shortcut if you have created one.

For this tutorial, we will be working with Windows 8.1 and Microsoft Excel 2013. Follow the following steps to run Excel on Windows 8.1

Click on start menu

Search for Excel N.B. even before you even typing, all programs starting with what you have typed will be listed.

Click on Microsoft Excel

The following image shows you how to do this

Understanding the Ribbon

The ribbon provides shortcuts to commands in Excel. A command is an action that the user performs. An example of a command is creating a new document, printing a documenting, etc. The image below shows the ribbon used in Excel 2013.

Ribbon components explained

Ribbon start button - it is used to access commands i.e. creating new documents, saving existing work, printing, accessing the options for customizing Excel, etc.

Ribbon tabs - the tabs are used to group similar commands together. The home tab is used for basic commands such as formatting the data to make it more presentable, sorting and finding specific data within the spreadsheet.

Ribbon bar - the bars are used to group similar commands together. As an example, the Alignment ribbon bar is used to group all the commands that are used to align data together.

Understanding the worksheet (Rows and Columns, Sheets, Workbooks)

A worksheet is a collection of rows and columns. When a row and a column meet, they form a cell. Cells are used to record data. Each cell is uniquely identified using a cell address. Columns are usually labelled with letters while rows are usually numbers.

A workbook is a collection of worksheets. By default, a workbook has three cells in Excel. You can delete or add more sheets to suit your requirements. By default, the sheets are named Sheet1, Sheet2 and so on and so forth. You can rename the sheet names to more meaningful names i.e. Daily Expenses, Monthly Budget, etc.

Customization Microsoft Excel Environment

Personally I like the black colour, so my excel theme looks blackish. Your favourite colour could be blue, and you too can make your theme colour look blue-like. If you are not a programmer, you may not want to include ribbon tabs i.e. developer. All this is made possible via customizations. In this sub-section, we are going to look at;

Customization the ribbon

Setting the colour theme

Settings for formulas

Proofing settings

Save settings

Customization of ribbon

The above image shows the default ribbon in Excel 2013. Let's start with customization the ribbon, suppose you do not wish to see some of the tabs on the ribbon, or you would like to add some tabs that are missing such as the developer tab. You can use the options window to achieve this.

Click on the ribbon start button

Select options from the drop down menu. You should be able to see an Excel Options dialog window

Select the customize ribbon option from the left-hand side panel as shown below

On your right-hand side, remove the check marks from the tabs that you do not wish to see on the ribbon. For this example, we have removed Page Layout, Review, and View tab.

Click on the "OK" button when you are done.

Your ribbon will look as follows

Adding custom tabs to the ribbon

You can also add your own tab, give it a custom name and assign commands to it. Let's add a tab to the ribbon with the text Guru99

1. Right click on the ribbon and select Customize the Ribbon. The dialogue window shown above will appear

2. Click on new tab button as illustrated in the animated image below

3. Select the newly created tab

4. Click on Rename button

5. Give it a name of Guru99

6. Select the New Group (Custom) under Guru99 tab as shown in the image below

7. Click on Rename button and give it a name of My Commands

8. Let's now add commands to my ribbon bar

9. The commands are listed on the middle panel

10. Select All chart types command and click on Add button

11. Click on OK

Your ribbon will look as follows

Setting the colour theme

To set the color-theme for your Excel sheet you have to go to Excel ribbon, and click on à File àOption command. It will open a window where you have to follow the following steps.

1. The general tab on the left-hand panel will be selected by default.

2. Look for colour scheme under General options for working with Excel

3. Click on the colour scheme drop-down list and select the desired colour

4. Click on OK button

Settings for formulas

This option allows you to define how Excel behaves when you are working with formulas. You can use it to set options i.e. autocomplete when entering formulas, change the cell referencing style and use numbers for both columns and rows and other options.

If you want to activate an option, click on its check box. If you want to deactivate an option, remove the mark from the checkbox. You can this option from the Options dialogue window under formulas tab from the left-hand side panel

Proofing settings

This option manipulates the entered text entered into excel. It allows setting options such as the dictionary language that should be used when checking for wrong spellings, suggestions from the dictionary, etc. You can this option from the options dialogue window under the proofing tab from the left-hand side panel

Save settings

This option allows you to define the default file format when saving files, enable auto recovery in case your computer goes off before you could save your work, etc. You can use this option from the Options dialogue window under save tab from the left-hand side panel

Important Excel shortcuts

Best Practices when working with Microsoft Excel

1. Save workbooks with backward compatibility in mind. If you are not using the latest features in higher versions of Excel, you should save your files in 2003 *.xls format for backwards compatibility

2. Use description names for columns and worksheets in a workbook

3. Avoid working with complex formulas with many variables. Try to break them down into small managed results that you can use to build on

4. Use built-in functions whenever you can instead of writing your own formulas

Summary

Introduction of MS Excel : Microsoft Excel is a powerful spreadsheet program used to record, manipulate, store numeric data and it can be customized to match your preferences

The ribbon is used to access various commands in Excel

The options dialogue window allows you to customize a number of items i.e. the ribbon, formulas, proofing, save, etc.

# data[].attributes.attachment_link.title
Introduction to Microsoft Excel 101: Notes About MS Excel

# data[].attributes.attachment_link.description
Microsoft Excel is a spreadsheet program that is used to record and analyse numerical data.Think of a spreadsheet as a collection of columns and rows that form a table.

# data[].attributes.attachment_link.readable_text
1. Select the cells you want to create a PivotTable from.

Note: Your data should be organized in columns with a single header row. See the Data format tips and tricks section for more details.

2. Select Insert > PivotTable.

3. This creates a PivotTable based on an existing table or range.

Note: Selecting Add this data to the Data Model adds the table or range being used for this PivotTable into the workbook's Data Model. Learn more.

4. Select OK.

By clicking the down arrow on the button, you can select from other possible sources for your PivotTable. In addition to using an existing table or range, there are three other sources you can select from to populate your PivotTable.

Get from External Data Source

Get from Data Model

Use this option if your workbook contains a Data Model, and you want to create a PivotTable from multiple tables, enhance the PivotTable with custom measures, or are working with very large datasets.

Get from Power BI

Use this option if your organization uses Power BI and you want to discover and connect to endorsed cloud datasets you have access to.

1. To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.

Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values.

2. To move a field from one area to another, drag the field to the target area.

If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh just one PivotTable, you can right-click anywhere in the PivotTable range, and then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, then on the ribbon go to PivotTable Analyze > select the arrow under the Refresh button, and then select Refresh All.

Summarize Values By

By default, PivotTable fields placed in the Values area are displayed as a SUM. If Excel interprets your data as text, the data is displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first selecting the arrow to the right of the field name, and then select the Value Field Settings option.

Next, change the calculation in the Summarize Values By section. Note that when you change the calculation method, Excel automatically appends it in the Custom Name section, like "Sum of FieldName", but you can change it. If you select Number Format, you can change the number format for the entire field.

Show Values As

Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.

Once you've opened the Value Field Setting dialog box, you can make your selections from the Show Values As tab.

Display a value as both a calculation and percentage.

Simply drag the item into the Values section twice, and then set the Summarize Values By and Show Values As options for each one.

You can change the data sourcefor the PivotTable data as you are creating it.

1. In the Insert PivotTable pane, select the text box under Source. While changing the Source, cards in the pane won't be available.

2. Make a selection of data on the grid or enter a range in the text box.

3. Press Enter on your keyboard or the button to confirm your selection. The pane updates with new recommended PivotTables based on the new source of data.

Get from Power BI

Use this option if your organization uses Power BI and you want to discover and connect to endorsed cloud datasets you have access to.

In the PivotTable Fields pane, select the check box for any field you want to add to your PivotTable.

By default, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area.

You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, drag it out from the list or uncheck it.

Summarize Values By

By default, PivotTable fields in the Values area are displayed as a SUM. If Excel interprets your data as text, it is displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields.

Change the default calculation by right-clicking any value in the row and selecting the Summarize Values By option.

Show Values As

Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.

Right-click any value in the column you'd like to show the value for. Select Show Values As in the menu. A list of available values displays.

Make your selection from the list.

To show as a % of Parent Total, hover over that item in the list and select the parent field you want to use as the basis of the calculation.

If you add new data to your PivotTable data source, any PivotTables built on that data source must be refreshed. Right-click anywhere in the PivotTable range, and then select Refresh.

If you created a PivotTable and decide you no longer want it, select the entire PivotTable range and press Delete. This won't have any effect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting the sheet is a fast way to remove the PivotTable.

Your data should be organized in a tabular format, and not have any blank rows or columns. Ideally, you can use an Excel table.

Tables are a great PivotTable data source, because rows added to a table are included automatically in the PivotTable when you refresh the data, and any new columns are included in the PivotTable Fields list. Otherwise, you need to either Change the source data for a PivotTable, or use a dynamic named range formula.

Data types in columns should be the same. For example, you shouldn't mix dates and text in the same column.

PivotTables work on a snapshot of your data, called the cache, so your actual data doesn't get altered in any way.

If you have limited experience with PivotTables, or are not sure how to get started, a Recommended PivotTable is a good choice. When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the PivotTable. This helps give you a starting point for additional experimentation. After a recommended PivotTable is created, you can explore different orientations and rearrange fields to achieve your desired results. You can also download our interactive Make your first PivotTable tutorial.

1. Select a cell in the source data or table range.

2. Go to Insert > Recommended PivotTable.

3. Excel analyzes your data and presents you with several options, as in this example using the household expense data.

1. Select a cell in the source data or table range.

2. Go to Insert > PivotTable.

3. Excel displays the Create PivotTable dialog box with your range or table name selected. In this case, we're using a table called "tbl_HouseholdExpenses".

4. In the Choose where you want the PivotTable report to be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, select the cell where you want the PivotTable placed.

PivotTable Fields list

In the Field Name area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes changing its appearance so easy.

PivotTable Fields list

Summarize by

By default, PivotTable fields placed in the Values area are displayed as a SUM. If Excel interprets your data as text, the data is displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first selecting the arrow to the right of the field name, and then by selecting the Field Settings option.

Next, change the calculation in the Summarize by section. Note that when you change the calculation method, Excel automatically appends it in the Custom Name section, like "Sum of FieldName", but you can change it. If you select Number... , you can change the number format for the entire field.

Tip: Since changing the calculation in the Summarize by section changes the PivotTable field name, it's best not to rename your PivotTable fields until you're finished setting up your PivotTable. One trick is to select Replace ( on the Edit menu) > Find what > " Sum of", and then Replace with > leave blank to replace everything at once instead of manually retyping.

Show data as

Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.

Once you've opened the Field Settings dialog box, you can make your selections from the Show data as tab.

Display a value as both a calculation and percentage.

Simply drag the item into the Values section twice, right-click the value and select Field Settings, and then set the Summarize by and Show data as options for each one.

If you add new data to your PivotTable data source, any PivotTables that were built on that data source must be refreshed. To refresh just one PivotTable you can right-click anywhere in the PivotTable range, and then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, and then on the ribbon go to PivotTable Analyze > select the arrow under the Refresh button, and then select Refresh All.

1. Select a cell in the source data or table range.

2. Go to Insert > PivotTable.

3. Choose where you want the PivotTable to be placed. Select Insert on new sheet  to place the PivotTable in a new worksheet or  select the cell where you want the new PivotTable placed in the Destination field.

4. Select Insert.

Typically, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes changing its appearance so easy.

If you add new data to your PivotTable data source, any PivotTables that were built on that data source must be refreshed. To refresh just one PivotTable you can select and hold on a cell anywhere in the PivotTable range, and then select Refresh. If you have multiple go to PivotTable tab on the ribbon and select Refresh.

If you created a PivotTable and decide you no longer want it, you can select the rows and columns spanning the entire PivotTable range, and then press Delete.

Use clean, tabular data for best results.

Organize your data in columns, not rows.

Make sure all columns have headers, with a single row of unique, non-blank labels for each column. Avoid double rows of headers or merged cells.

Format your data as an Excel table (select anywhere in your data, and then select Insert > Table from the ribbon).

If you have complicated or nested data, use Power Query to transform it (for example, to unpivot your data) so it's organized in columns with a single header row.

Create a PivotChart

Calculate values in a PivotTable

Delete a PivotTable

# data[].attributes.attachment_link.title
Create a PivotTable to analyze worksheet data

# data[].attributes.attachment_link.description
How to use a PivotTable in Excel to calculate, summarize, and analyze your worksheet data to see hidden patterns and trends.

# data[].attributes.attachment_link.title
What is Data Mining and Why is it Important?

# data[].attributes.body
Additional resources/notes : 1. https://www.learntek.org/blog/data-mining-examples-and-techniques/ 2.https://www.precisely.com/blog/datagovernance/top-5-data-mining-techniques 3.https://bootcamp.pe.gatech.edu/blog/10-key-data-mining-techniques-and-how-businesses-use-them/ 4.https://www.guru99.com/best-data-mining-tools.html

# data[].attributes.headline
Chapter 6 - Display Tabular Data in Excel (Part 1)

# data[].attributes.subject
Chapter 6 - Display Tabular Data in Excel (Part 1)

# data[].attributes.headline
Chapter 6 - Display Tabular Data in Excel (Part 2)

# data[].attributes.subject
Chapter 6 - Display Tabular Data in Excel (Part 2)

# data[].attributes.attachment_link.title
Fruit_Sales_Data with pivot table (JIHA)

# data[].attributes.headline
NUR ADRIANA BATRISYIA BINTI MOHD NASIR (AM2304013595)

# data[].attributes.subject
NUR ADRIANA BATRISYIA BINTI MOHD NASIR (AM2304013595)

# data[].attributes.attachment_link.title
Fruit_Sales_Data with pivot table (Danial Ayman Mikael)

# data[].attributes.attachment_link.title
Fruit Sales Data_AUNI IMTIYAZ BINTI ABDUL RAHMAN

# data[].attributes.body
amirul asyraf

# data[].attributes.headline
NUR SALSABILA SHAHIRA BINTI MOHD GHAZALI

# data[].attributes.subject
NUR SALSABILA SHAHIRA BINTI MOHD GHAZALI

# data[].attributes.headline
MUHAMMAD AMEERUL HAZIQ BIN MOHD RAZALI (AM2304013214)

# data[].attributes.subject
MUHAMMAD AMEERUL HAZIQ BIN MOHD RAZALI (AM2304013214)

# data[].attributes.body
Aeerul exercise

# data[].attributes.attachment_link.title
Fruit_Sales_Data with pivot table (sect 1) latest (JIHA)

# data[].attributes.headline
EXER 1-PUTRI AINA NURSYAMIMI BINTI ABD MALIK

# data[].attributes.subject
EXER 1-PUTRI AINA NURSYAMIMI BINTI ABD MALIK

# data[].attributes.attachment_link.title
Fruit_Sales_Data - SITI NAZIRAH BINTI SALIM

# data[].attributes.attachment_link.title
Fruit_Sales_Data - ANIS AZIRA BINTI RUDI NOR

# data[].attributes.attachment_link.title
Fruit_Sales_Data (NURUL IZZAH ALYA ZAINUDDIN)

# data[].attributes.headline
 EXERCISE 1- NURUL AUNI DAMIA BINTI MANSOR

# data[].attributes.subject
 EXERCISE 1- NURUL AUNI DAMIA BINTI MANSOR

# data[].attributes.headline
EXERCISE 1 NUR SHASHA SHAHIRAH BINTI ZAKARIA 

# data[].attributes.subject
EXERCISE 1 NUR SHASHA SHAHIRAH BINTI ZAKARIA 