We then design a cell formatting which we like and proceed to apply the formula on all cells

We can then go to “Conditional Formatting”  “New Rule”  “Use a formula to determine which cell to format”  we will have to create two formulas, one for odd and one for even weeks. We then design a cell formatting which we like and proceed to apply the formula on all cells

Paste formatting

Paste formatting The easiest way to apply the formatting and only the formatting is by copying a cell that has the formatting rules (in our case B1) and then pasting the formatting on the other cells. Excel has a vide variety of Pasting options due to the complexity of it’s cells. In this particular one, “Paste Formatting” will only paste the formatting rules from the original cell and nothing else.

PASTING Options

PASTING Options Paste – will paste all the information from the copied cell (formatting/formulas or cell content) Paste Values – will only paste the content of the cell – in case the cell contains a formula, only the result of the formula will be pasted; Paste Formulas – will only paste the formula from the copied cell;

Paste Transpose – will paste everything just like a regular paste but every reference of the cell will be transposed; Paste Link – will paste a reference to the original copied cell;

PASTING Options

PASTING Options

Filtering and sorting

Filtering and sorting Excel provides two very simple solutions for organizing large amounts of data: Filters and Custom Sorting. Filters – a built-in function of Excel that allows for an easy filtering or sorting of large chunks of data based off: List Values – For example, sorting all data on a column in Alphabetical order; Format – Filtering all data on a column based on cell formatting options such as cell colors;

Criteria – Filtering all information on a column based on a specific criteria (different for data containing Text or Numbers); While sorting information will only re-arrange it based on a given criteria, filtering data will “hide” the rows that do not comply with the given criteria. In order to apply a filter on specific data, all we have to do is to select the table and go to “Sort & Filters” and select “Filter”.

This can also be achieved by selecting the “DATA” mail tab and clicking on the “Filter” button.

4. “Filter by color” will hide all other data that does not have the same color as the option chosen;

Once the filter is applied, we can sort information using the options mentioned earlier. This can be done by clicking on the “Sort” button

1. By default, “Sort Smallest to Largest” and “Sort Largest to Smallest” will be the first two options in any filter applier. This function works for both numerical and string (text) data. For text data it will arrange the information in an Alphabetical or Un-alphabetical order (and the name will change to “A->Z” or “Z->A”);

2. “Sort by color” will present several options based on the different colors found throughout the column; 3. “Clear Filter From COLUMN NAME” will remove any filter from the table. However, it will not return the data to it’s previous state if we are to sort the information; 4. “Filter by color” will hide all other data that does not have the same color as the option chosen;

5. “Number/Text Filters” has several default options such as Greater Than, Top 10, Contains (specific text) and so on; 6. “Search” option allows for us to search for a specific number/text inside the filtered information.

A few examples of how sorting and filtering can help us manipulate data:

A few examples of how sorting and filtering can help us manipulate data: 1. Sort Smallest to Largest (Alphabetical order if the data is text) 2. Sort by color 3. Filter by color 4.Clear Filter From COLUMN NAME 5.Number filter (on column C) – Greater Than 5 SORTING EXAMPLES

Using the “Search” function to sort data based on a specific criteria.

Using the “Search” function to sort data based on a specific criteria. If we were to type the letters “Ja” in the “Search” bar for column B (Name) the details below will already showcase all the results that contain those two letters. After applying the filter Applying a similar filter on the “Sales” column – all values that contain the digit 4

Filtering vs Hiding rows

Lastly, there is a simple difference we must understand between sorting data and filtering. Filtering occurs through hiding the data that does not meet the input criteria, while sorting will keep all data and sort it in a specific way.

Although filtering will hide the rows not matching the information desired, trying to unhide that data will not work. Hidden data through filters will look similar to that of normally hidden rows, but the color of the row numbers will be blue. Filtering vs Hiding rows The easiest way of identifying data which was hidden is a double line which occurs between lines which were hidden. HIDDEN ROWS: 1012->1013 1015->1021

FILTERED ROWS

In the example below we will apply 3 sorts on a table in order to have them ordered properly.

Custom Sorting – this function allows for a fast sorting of large amounts of data by applying several sorting options on different columns of a table. In the example below we will apply 3 sorts on a table in order to have them ordered properly. The “Custom Sort” option can be selected from the same menu as the “Filter” option, after selecting our table.

“Sort By” – will display a list of the available columns in the table;

Custom Sorting allows for an unlimited amount of sorting levels. This can be done by using the “Add Level” button. “Sort By” – will display a list of the available columns in the table; “Sort On” – will allow for 4 sorting options: “Values” / “Cell Color” / “Font Color” / “Cell Icon” “Order” – will display sorting options based on the “Sort On”; For example:

If we select “Values” in the “Sort On” category, we can choose “A-Z” in the “Order” category. If we select “Cell Color” in the “Sort On” category, we can choose colors from the ones available in that specific table column; In our example we will have the information ordered by 3 criteria: First Level – Queue sorted A-Z Second Level – Number of days sorted Smallest to Largest

Third Level – Norm sorted Largest to Smallest

You can see the Queue is ordered in an alphabetical order.

You can see the Queue is ordered in an alphabetical order. Each Queue section has the number of days sorted from low to high If two agents from the same Queue have the same number of days, they will be separated by the third level – Norm – from high to low Finished sorted table

Hiding, Deleting and Inserting Rows/Columns

Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0;

Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E.

Charts are one of the most common ways to present data visually. This module walks you through creating and modifying charts in Excel

Excel has several different types of charts, allowing you to choose the one that best fits your data. In

Excel has several different types of charts, allowing you to choose the one that best fits your data. In order to use charts effectively, you'll need to understand how different charts are used. For the following examples, we are going to use a standard dataset representing the number of sales for 5 different departments for 5 different years.

Let’s start by inserting a chart. We are first going to use a simple “vertical bars” chart and try all of

Let’s start by inserting a chart. We are first going to use a simple “vertical bars” chart and try all of the common options to see how they can all enhance or decrease the amount of clarity provided to the data. To insert a chart, we first need to select the Raw Data which will be showcased in the chart and then go to the “Insert” Main Tab and select either the wanted chart or the “Recommended Charts” option.

Column Charts – uses vertical bars to compare data

The following window will come up if we click on “Recommended Charts” allowing us to choose from a preset which Excel will find most useful. We can also click on “All Charts” to get the full range or data charts available. Column Charts – uses vertical bars to compare data Line Charts – ideal for showcasing trends Pie Chart – proportion comparison Bar Charts – same as column charts but horizontal

Area Chart – similar to line charts but the areas under the lines are filled Surface Charts – a 3D landscape ideal for large data sets

Colors and dimensions which can be primarily done from the “Format” Main Tab

In the first example we are going to chose a Column Chart. The following image will appear in our document, showcasing the differences between department sales for each individual year. Next off, Excel provides a wide range of customization tools in order to modify the data so that we can see exactly what we want from it.

Customization can be done through the “Design” and “Format” Main Tabs. There is enough customization to modify the data in any way shape or form we need. The most basic options include: Data Labels, chart titles, legends, gridlines and trends (all which can be done from the “Add Chart Element” option Colors and dimensions which can be primarily done from the “Format” Main Tab

These will pop-up in the upper-right corner of any chart after you click on it

We can also edit the data for our chart, swap the axis and even change the chart type with ease using the options under the “Design” Main Tab Charts also provide a few handy side options to further filter the data as well as quickly adding chart elements and changing chart colors. These will pop-up in the upper-right corner of any chart after you click on it

A few examples of changing chart types as well as adding elements:

A few examples of changing chart types as well as adding elements: Speaker notes: Right-Click to save chart template and use it later!

Hiding, Deleting and Inserting Rows/Columns

Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0;

Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Working with Multiple Worksheets & Workbook

This module is all about working with multiple worksheets and workbooks. Learn how you can combine data, manage datasets and perform calculations across multiple sources

RANDOM FACT #101

RANDOM FACT #101 You cannot name a Sheet “History” The reason for this is a special and not so well known Excel feature which keeps a tracking record of changes made in a document. In order to check a file History, you need to press the following keyboard combination (in succession, not simultaneously): ALT+T+T+H

This will open a window which allows for a few filters regarding the changes we want to see. Upon confirming, a sheet will be created named “History” which will contain a list of the changes made in the current file:

As stated previously, Excel offers the option of creating multiple worksheets which can all contain data

As stated previously, Excel offers the option of creating multiple worksheets which can all contain data or formulas, as well as opening separate Excel instances (or workbooks) and they can all be referenced together.

Let’s start with referencing between worksheets – or simply “sheets”. Just like we have learned, the physical address of a cell of given by the Column & Row name/number. However, when working between sheets, in order to differentiate a cell – say, A1 – we must also add the Sheet Name in front of the cell address.

Thus, A1 from Sheet1 will be come Sheet1!A1 and A2 from Sheet 2 will be Sheet2!A1. Luckily, we don’t have to write the actual address of the sheet if we want to reference something from another part of the workbook. By switching workbooks while working on a formula, Excel will keep the formula active in the formula bar and continue to take references of whatever we click on. There are formulas that will require typing the Sheet Name manually, but we’ll get to that later.

A cross-workbooks reference will look like this:

A cross-workbooks reference will look like this: =[Book1]Sheet1!$H$7 Book1 being the name of the document being referenced while Sheet1 being the actual sheet where the cell is located.

Referring between workbooks is just as easy, but instead of having only the Sheet Name, the address will also acquire the document name – as long as it is still open! If by any change we close the document, Excel will even keep a physical address of the document to keep the reference alive. If by any chance the document gets erased, an error will be returned instead of the reference name – “REF!” – and if the formula in which the reference was used can continue without it, it will simply ignore the “REF!” alert.

Speaker notes: By default, cross worksheets or workbooks references will automatically block the row/column reference with “$”.