• 5.1 Tools for processing different types of data arrays
  • 5.1.1 Subtotal
  • 5.1.2 Pivot tables and charts
  • 5.1.3 Data consolidation
• 5.2 Data protection in Excel documents
  • 5.2.1 Cell protection
  • 5.2.2 Worksheet protection
  • 5.2.3 Protecting individual Excel workbook elements (structures and windows)
  • 5.2.4 Password protection of the Excel workbook file that prohibits viewing and making changes

5.1 Tools for processing different types of data arrays

In practice, we deal with a different data array presented in a single table.

For example, a table can have a column with text data, a column with numbers, or a separate column with dates.

They are convenient specialized data analysis tools (without formulas and functions) to process such data types.

These tools include:

• Subtotal

• Pivot tables

• Pivot charts

• Data consolidation

These are the most common Excel tools to process data sets in a single table.

A pivot table is a "table that can be rotated"; it allows us to create visual reports for large volumes of data.

A pivot chart summarizes and visualizes large volumes of data (it can be created for both pivot and regular tables).

Consolidation combines data located in different tables into a single table.

Let's take a closer look at these tools.

 

5.1.1 Subtotal

Subtotal is a tool that allows us to group data of the same type and get interim results (subtotals) based on grouped data.

To use the tool Subtotal, we need the following:

1. Sort data by one or more attributes. Sorting is performed according to the criteria that need to be used to get interim results

2. Run the command: Data –> Outline –> Subtotal

3. Configure dialog box Subtotal settings

 

Let's apply the tool Subtotal to calculate the total cost of the ordered product for each customer company for the table:

First, let's perform a two-level sorting:

Level 1 sorting – by client company,

Level 2 sorting – by order date:

We have these results:

Next, we run the command: Data –> Outline –> Subtotal and configure the settings in the dialog box:

• when the Company Name field is changed – the field Client company

• sum up using a summing operation Sum

• add total by field Cost

 

As a result, we get several levels of data grouping in the table:

1. 1 – total results for all data
2. 2 – results for each group of customers
3. 3 – detailed results

In the table, we can also set up several more levels of subtotals based on the desired data.

 

For example, if we need to track changes in funds spent on the product by Order Date.

Run the command: Data –> Outline –> Subtotal, and configure the settings in the dialog box:

• when there are changes – the Order data

• sum up using a summing operation Sum

• add total by field Quantity of ordered goods

• we must uncheck the box Replace current subtotals (to add results to existing totals)

 

Now we have 4 levels of data grouping:

 

If we do not need totals but need to return the table to its original state, we can run the command: Data –> Outline –> Subtotal –> Remove All.

 

5.1.2 Pivot tables and charts

In many situations, when we have a large volume of different types of data, we need to analyze or filter it according to specific criteria to draw meaningful conclusions.

A pivot table is a powerful data analysis tool.

Let's consider basic commands for working with pivot tables.

To create pivot tables:

• Place the cursor in any cell in the data table

• Run the command: Insert → PivotTable → From Table/Range

• configure settings in the dialog box

 

For example, let's analyze business activities and quickly summarize a large volume of data.

Imagine owning several stores located in different geographical areas of the country (or even abroad). Your stores sell various food products. You have a large volume of data, and the table is too large. Therefore, as an owner, to draw certain conclusions about sales, you need to track thousands of data entries (in total, the table contains 924 records).

Imagine you need to analyze sales data, namely:

get information about the total number of products sold and the total revenue received for each year separately, for each product group, for each product type, and for each store.

To do this, follow these steps:

1. Place the cursor in any data cell in the source table

2. Run the command: Insert → PivotTable → From Table/Range

3. Click OK to open the designer panel, which contains PivotTable Fields and 4 separate areas for the location of these fields.

 

We build a report table by dragging the table's field (column) headers to the desired area. This creates the type of table we need.

In the ROWS area, move precisely the fields we need to summarize data for: first Product group, then Product type, and Store location.

The COLUMNS area: in our example, we don't place fields in this area. We will only check the reporting information here.

In the VALUES area: values for fields in this area are automatically summed up (the data for the fields we move here are summed). For example, since we want to determine the total number of products sold and the total cost of products sold, we will place the fields Number of units and Proceeds into this area.

The FILTERS area; the fields that need to be used for cutting data are moved to this area. For example, we're moving the field Year here; now, we can get separate reporting information for any year separately.

We can change the calculation in the VALUES area to another one (for example, calculate average values, maximum, minimum, etc.). To do this, click the Data Field, select Value Field Settings, and specify the desired arithmetic operation.

Click OK and we obtain this result:

Now the data are sorted: for each product group, for each product type, and for each store, the total number of products sold and total revenue are displayed, and we can also filter it by year.

You can quickly modify the pivot table by moving fields from the ROWS area to the COLUMNS area and vice versa (this changes the appearance of the table and its organization).

You can also visually summarize the reporting data using pivot charts. Pivot charts can only be constructed based on pivot tables.

Graphical visualization of data using charts always allows us to better analyze data.

To create a pivot chart:

• Convert the table to the desired view (the conversion depends on what data we need to show in the chart; we can build multiple pivot charts of different types for the same pivot table)

• Run the command: PivotTable Analyze → PivotChart

• select the desired chart type

 

 

Let's create a report chart for the pivot table that was shown above.

To create a report chart, follow these steps:

1. Move the Year field from the FILTERS area to the ROW area

2. Remove the Proceeds field from the VALUES area

3. In the pivot table, close all fields by Year field in the Row names column

4. Move the Product Group field to the COLUMNS area

After the transformations, the pivot table took the following form:

5. Place the cursor anywhere in the resulting pivot table.

6. Run the command: PivotTable Analyze → PivotChart , and select the chart type – clustered column (if necessary, you can change the chart type to another one).

 

We obtain this result.

 

Now we can see sales of which product group were the highest and lowest, and for which year.

5.1.3 Data consolidation

Data consolidation is a built-in option in Excel that allows us to combine data into one table from different tables and combine data located on other worksheets (in the same or in separate files) into one table.

When consolidation can be helpful.

For example, people engaged in business for different periods often receive data on their activities from other branches or regions.

Therefore, it becomes necessary to automatically combine the received information on one worksheet with the reflection of the changes presented in individual documents.

To do this, using the built-in Excel tool – Data consolidation – is convenient.

To create consolidated tables:

1. Place the cursor in any cell on the worksheet where you want to get the consolidation result

2. Run the command: Data → Consolidate

3. In the dialog box Consolidate configure the necessary settings

 
 

Let's consider a practical example. Imagine we have the following problem statement: a company sells products in several regions of the country simultaneously and receives reporting information from these regions once a quarter (once every three months).

We have two separate reporting files from the eastern and western regions: Sales in the west (contains data on the number of sales of different types of products in the western region) and the same Sales in the east (includes data on the number of sales of various types of products in the eastern region).

                             

We need to create a report file summarizing the total sales volumes for each product type for each month in one table.

Place the cursor in any cell on the worksheet of the Excel report file where you need to get the consolidation result.

Run the command: Data → Consolidate

In the dialog box Consolidate put a link to the data that needs to be consolidated (combined with each other) and configure it:

 

• Checkbox Create links to source data displays current changes in the consolidated table when changes are made to individual data files.

• Checkboxes in the section Use labels in, namely: Top row and Left column allow us to automatically create similar labels in a consolidated table.

We obtain this result.

 

5.2 Data protection in Excel documents

 

Microsoft Excel has several levels of security that allow us to control access to MS Excel data:

• cell protection

• worksheet protection

• workbook protection (structure and window protection)

• protecting the entire file from being viewed and modified

Let's take a closer look at these protection features.

5.2.1 Cell protection

To enable cell protection:

• select cells to be locked

• open the context menu (called with the right mouse button)

• select the item Format Cells

•  go to the tab Protection

​

In the dialog box, on the tab Protection, there are two options: Locked and Hidden.

Check the box in the field Locked or Hidden; this does not mean that the cell will be immediately protected from changes and formulas will be hidden from the user. These security tools are activated only after the entire worksheet is protected.

Hiding formulas means hiding formulas directly and displaying any cell content in the formula bar.

You can protect not all cells in the worksheet, but only part of them, while allowing other users to enter and change data in unprotected cells.

To unlock cells:

• select the desired range of cells,

• right click, select the context menu item Format Cells,

• in the window Format Cells, go to the tab Protection,

• uncheck the box Locked.

 

5.2.2 Worksheet protection

To set protection on a worksheet, go to the menu item Review → Protect Sheet:

 

In the dialog box Protect Sheet we need:

• set a password to unprotect the worksheet

• select the options for those actions that are allowed for the user to perform on the protected worksheet

 

 

5.2.3 Protecting individual Excel workbook elements (structures and windows)

Workbook worksheets can be hidden and displayed only for viewing. We can hide one or several worksheets by grouping them first. We can't hide all the workbook worksheets. At least one of them must always be displayed. Data stored on hidden worksheets remain available for calculations.

If we protect the workbook after hiding the worksheets, the worksheet cannot be displayed for viewing unless we first remove the protection from the workbook.

To protect the workbook:

Run the command: Review → Protect Workbook

 A dialog box Protect Structure and Windows appears

When a structure is protected, we cannot:

• view hidden worksheets.

• move, delete, hide, or change worksheet names,

• copy worksheets to another workbook, and

• insert new worksheets and worksheets with charts, while we can add charts to existing worksheets.

• record new macros

• use the standard add-in analysis tool Data Analysis to place the results on a new worksheet.

When a window is protected, we cannot:

• change the size and position of the open workbook, as well as

• move, resize, and close windows.

We must remove the workbook protection (structure and windows) to restore access to locked workbook elements.

5.2.4 Password protection of the Excel workbook file that prohibits viewing and making changes

This data protection method in Excel provides optimal security by restricting access and preventing unauthorized file opening. We can set two separate passwords for opening (viewing) the file and for making changes to the file. Password protection uses encryption to open and view the file. However, the password for making changes to the file is not encrypted.

We can set a password to open the file on the File in the group Info in the button menu Protect Workbook by selecting Encrypt with Password:

 

After that, a window will appear (where a user should type the password) to open the protected document:

The password confirmation window appears to avoid accidental errors when entering a password. Unfortunately, Excel doesn't have standard tools for recovering forgotten passwords. To prevent the loss of passwords, we recommend creating them so that you can remember or keep the file with the passwords separately.