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. Formatting

Formatting helps to highlight key messages and make the data presentable. This module covers several formatting tools like font formatting, borders, alignment, number formatting, as well as the Excel styles and themes

Basic cell formatting options

Basic cell formatting options These basic options can be accessed through the “FONT” or “ALIGNMENT” categories under the “Home” Main Tab. Borders – allows for the addition of different types of borders for the selected cells Text Alignment – several options for changing the position of text inside a cell Wrap Text – increases the size of the row in order to fit all the information in a cell;

Merge & Center – merges multiple cells into one (The new cell will keep the address of the first cell) Wrap Text OFF Wrap Text ON

Merging Cells

Merging Cells Excel allows for the merge of multiple cells as long as the obtained shape is a square or rectangular. Text inside merged cells will automatically be centered but it won’t be aligned in the middle. Merged cells will keep the address of the first cell – B2 in the second example and A2/A4 in the third example.

These options can be accessed under the “FONT” menu in the “HOME” Main Tab.

Excel sheets are split into cells by gray borders. However, there is an option to add extra borders with different colors and style in order to properly highlight important data and create well defined tables. These options can be accessed under the “FONT” menu in the “HOME” Main Tab. Excel provides multiple options for designing our tables such as one-sided borders, different style borders and line colors.

In the screenshot below the “All Borders” option was used for cells B3:F13 Cell Borders

Cell Formatting

Cell Formatting Excel offers a wide variety of cell formatting options. Formatting options allow for the Cell Displayed Value to be changed without affecting the input data or Cell Real Value. The formatting menu can be easily accessed from the “Home” Main Tab under “Number Format” section or by pressing CTRL+1.

Default Formatting Options

Default Formatting Options By default, excel offers 11 formatting options, each having multiple variations which can be accessed through the “More Number Formats…” General – Default formatting – it will automatically change if a specific value is inserted; Number – The Cell Real Value will be automatically detected as numbers and will allow for the addition of more decimal places;

Currency – Will convert the value into currency (multiple options available); Accounting – Similar to the Currency formatting but with better alignment options; Short Date – Will change the formatting of a date to DD/MM/YYYY ; Example: 09/20/2017; Long Date – Will change the formatting of a date to DDDD, MMMM DD, YYYY ; Example: Wednesday, September 20, 2017 Time – Will convert the value in a HH:MM:SS AM/PM;

Example: 12:00:00 AM ; Percentage – Will covert the given value in a percentage; Fraction – Will covert the given value in a fraction; Scientific – Will covert the given value in a scientific format; Text – Will ignore the format of the input value and treat all characters as strings (plain text);

In the following example we will cover the value “1” in all the default formatting's:

In the following example we will cover the value “1” in all the default formatting's: General – 1 Number – 1.00 – by default will add 2 decimal places Currency – $1.00 – by default will add the currency as dollar Accounting – same as in currency but will provide a special alignment

Short Date – 1/1/1900 – the reason for this strange date is the perception excel uses to store dates; All dates are attributed a number which is the equivalent of the number of days that have passed since the 1st of January, 1900. This means that the 20th of September will be stored as 42998 (days since 01/01/1900) Long Date – Saturday, January 1, 1900

Time – 12:00:00 AM – the reason for this similarly strange formatting is the perception Excel uses to store time. All hours are being processed as a fraction of 1 divided by something. Excel understands 1 as the hour 12:00:00 AM, thus if we were to put in 0.75 it would result in 06:00:00 PM and 0.5 will be 12:00:00 PM. Percentage – 100.00% Fraction – 1 Scientific – 1.00E+00

Another example with 0.5 on the C column and 09/24/2017 on column D

Another example with 0.5 on the C column and 09/24/2017 on column D

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 Data

This module is all about working with data – and making it easy to work with. This week you will learn how you can manage your spreadsheets – find data with Filter and Sort, retrieve and change data using Find and Replace, and use Conditional Formatting to highlight specific data

Conditional Formatting is a simple but extremely important tool in Excel that allows the user to apply sp

Conditional Formatting is a simple but extremely important tool in Excel that allows the user to apply specific sets of rules on a batch of data that will automatically color and/or change the format of that data. Rules may vary from greater/smaller numbers all the way to complicated formulas.

Conditional formatting can be accessed through the “Styles” Menu in the “Home” Main Tab. For the following examples we will use the table below presenting statistics about Sales for 10 employees.

Highlight Cells Rules:

Much like all excel options, Conditional Formatting offers a handful of pre-defined conditions that are easy to apply. Keep in mind that these options need to be applied on a selected range, otherwise they will not have any effect. Highlight Cells Rules: Greater Than… / Less Than… – based off a given number, highlights all cells that meet the condition;

Between… / Equal To… – highlights all cells that meet the conditions (between A and B or equal to C); Text that Contains… – highlights all cells that contain a given letter or word; A Date Occurring… – overs a large variety of options in order to highlight specific cells formatted as “DATE” (such as Next Month, Last week, and so on); Duplicate Values… – highlights all cells in the given range that are identical;

Top/Bottom Rules: Highlights cells based of given conditions (Top 10 Items… / Top 10%… / Above Average… and so on); Data Bars: Offers a wide variety of colors to highlight a percentage of a cell based off a given condition (for example, if James has 79 Sales and we set the condition to 100, then 79% of the cell will be colored in order to highlight the progress made towards achieving the target); Color Scales:

Multiple options that highlight numerical values based off their average (for example, if our given values range between 1 and 100 and we select the first option “GREEN-YELLOW-RED”, the bottom-most values will be red, values around 50 will be Yellow and the top values will be green); Icon Sets: Different types of icons used to showcase differences between numerical values based off their average;

In our first example we are going to try and apply a color set on the Sales Rate column in order to prope

In our first example we are going to try and apply a color set on the Sales Rate column in order to properly showcase our 9% target.

We can start by applying a default color scheme such as “Greater Than…” and have the value set to 9% with a “Green Fill with Dark Green Text”. We can then apply a second rule using “Less Than…” and use the same value of 9% but this time with “Light Red Fill with Dark Red Text”. This way, all values below 9% will be Red and all values above 9% will be Green. However, in this particular case we are left with no color for cells that contain the exact value of 9%.

In order to avoid such a possibility, we will use a third rule through “Equal To…” and select 9% as the value with “Yellow Fill with Dark Yellow Text”.

Next off we are going to create a rule to showcase the differences in “Sales volume change”.

Next off we are going to create a rule to showcase the differences in “Sales volume change”. Going through the available options for conditional formatting we chose “Data Bars” as it seems to be the most accurate way to highlight the changes in Sales Volume. Once applied, the rule will automatically add colored bars to show the progress/regress of the changes in Sales Volume,

Each conditional formatting rule has in-depth options which can be modified by using the “Manage Rules…”

Each conditional formatting rule has in-depth options which can be modified by using the “Manage Rules…” option under “Conditional Formatting”. Before opening this menu, make sure to select at least one of the cells to which the rule applies.

Rule types can be easily changed to the ones that would best highlight our data.

All options available to this type of formatting are displayed in the first cell. Next off we have a handful of secondary options followed by a third and final option. The number of options may vary for each type of formatting (I.E. “Date Occurring” only has one secondary option) Rule types can be easily changed to the ones that would best highlight our data.

The first 4 options allow for the formatting of data based on simple conditions, while the 5th one only formats data based on an Excel Formula. The “Format” button opens a new window which us to create a custom format which will then be applied to all cells that meet the formatting condition. Speaker notes: COPYING FORMATTING WILL MULTIPLY THE RULES!

Formatting based on a formula

In order for the Formula Formatting to function we must have at least one logical operator, otherwise there will be nothing to guide the rule towards Formatting based on a formula Consider the following example:

Before filling the info in this table we want to find an efficient way to color the date headers according to weeks. For 10 days it would be easy to just manually color them, but if we are to use this on a daily basis, it would amount to a large amount of unnecessary work which can be avoided by using a formula to color the cells automatically.

=WEEKNUM(B3,2)

First we are going to isolate the data in order to create an excel formula and test it. We took the dates on a separate sheet and decided to create the conditional formatting based on the week number. First, we used the formula =WEEKNUM which returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1.

We then used the formula =ISEVEN in order to simply but elegantly differentiate odd weeks from even ones. This way, by uniting the formulas, even week numbers will have the final result TRUE while odd ones will return FALSE. =WEEKNUM(B3,2) =ISEVEN(C3) Final formula: =ISEVEN(WEEKNUMB(B3,2))

Speaker notes: ! When writing the WEEKNUM formula, the second index (return_type) is a numerical value which allows the formula to determine which day of the week should be the starting day (1-Saturday, 2-Monday, and so on)