Status can be “Source is open” or “Error: Source not found”

A very common problem which you may encounter with Excel which can be extremely stressful and annoying is the double-Excel-instance. Basically, when referencing another workbook in a formula, Excel will simply not recognize other workbook. When switching to the other workbook, you will be able to navigate and edit the date while the formula you were creating in the original workbook will be in a “pending” status.

This is caused by Excel opening two instances in your computer, two individual “Excels” which are completely individual from each other. Unfortunately, the only to resolve this matter is to shutdown the workbook that did not open under the same Excel process and re-open it. Status can be “Source is open” or “Error: Source not found”

The Location section will display the actual PC address of the referenced workbook – if the workbook was not saved, it will be blank. We can easily check all connections to other excel workbooks by using the “Edit Links” option under the “Data” Main Tab – “Queries & Connections” section. Upon using the option, the following will be displayed:

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. Text and Date Functions

By the end of this module, you will be an expert in Date and Text functions. This module discusses ways you can extract information and manipulate data to fulfil specific business requirements

RANDOM FACT#31

RANDOM FACT#31 FLIGHT SIMULATOR Very few people know, but Microsoft Excel 1997 had an amazing Easter Egg – The program included a flight simulator in which you could explore the environment with the mouse. you can move it back and forth to vary the pitch of your ‘craft’ and sideways to roll it

Text and Date Functions

Text and Date Functions Every now and then, the working data will not be in a proper formatting for interpreting, thus leaving us with no option to manipulate the information. Luckily, instead of trying to change the source data, we can use various TEXT and DATE functions to modify the data in our advantage. Lets start with the basic formulas for this module:

=TEXT(value,format_text) – this formula will take a cell and modify it’s value in a specific format; =LEFT(text,[num_chars]) – will return x characters (num_chars) from a cell starting from the left side.; =RIGHT(text,[num_chars]) – same as LEFT but from the right side; =MID(text,start_num,num_chars) – will return x characters (num_chars) from a cell starting from the y number (start_num)

=DATE(year,month,day) – will compose a date based off the imput values (numerical only) And here’s how they work, on a few simple examples: =TEXT(43022,"dd-mmm-yyyy") – will return 14-Oct-2017; =LEFT(“Hello”,2) – will return “He”; =RIGHT(“Hello”,2) – will return “lo”; =MID("Hello",2,3) – will return “ell”; =DATE(2017,10,14) – will return 10/14/2017

The first two characters are always the DAY of the first date;

Let’s take a practical example. The following data is a report which is exported as Text, which means that it cannot be properly filtered or interpreted as Dates. We are going to try to compilate this data in two different columns, each representing the start and end date of these periods:

If we look through the information, we can identify a few simple trends which will hep will separating the information before we create our dates: The first two characters are always the DAY of the first date; The 4th and 5th characters are always the DAY of the second date; The 7th and 8th characters are the MONTH of each date; The last 4 characters are the year of each date;

We are going to separate these details on individual columns before we use the DATE function to create our new dates;

And this is how it all looks like in Excel:

And this is how it all looks like in Excel:

=CONCATATENATE(A1,B1); or =A1&B1; in both cases, the output will be the same;

Now let’s include the concatenate and text functions into example. Concatenate is a simple excel function which can be achieved either by typing =CONCATENATE or by using the “&” symbol between different “text” characters. For example, if we want the output of a cell to display the data from two different cells (say, A1 and B1) but combined, we could use:

=CONCATATENATE(A1,B1); or =A1&B1; in both cases, the output will be the same; Let’s try to further complicate our example. We are going to use the H column to type the following standard message for each of our dates: Started with “FIRST DATE” and ended with “SECOND DATE” Upon typing =“Started with “&F2&” and ended with “&G2 in H2 we should get the wanted result.

However, the following value will be displayed in the cell “Started with 43180 and ended with 43184” The reason for this is the formatting of the cells – Both F2 and G2 contain DATE values, which mean that, when called in the CONCATENATE function, they will be displayed as their real values instead of our chosen formatting. And here’s where the TEXT function comes in.

Speaker notes: Text has to be added with “” in order for the formula to consider it valid!

The TEXT formula works with all basic formatting's available in the “Format” section of excel

To save our formula, we will use the TEXT function as if we were formatting a cell in the DD-MMM formatting: The TEXT formula works with all basic formatting's available in the “Format” section of excel

DD/MM/YYYY – 04/12/2013

Before we wrap up, here are a few examples of the more popular format_text options for the TEXT function: DATES – “D” is short for DAY, “M” short for MONTH and “Y” short for year. These can be combined in a various number of ways, to showcase the exact value we desired: DD/MM/YYYY – 04/12/2013 DDD-MM – WED-12 DD-MMMM-YY – 04-December-12

HOURS – “H” is for HOUR, “M” for MINUTE and “S” for SECOND – additionally we can use “PM” or “AM”: H:MM AM/PM – 1:29 PM HH:MM:SS – 01:29:35 PERCENTAGE – 0.00% will translate in a number with 2 decimals and the “%” sign FRACTION – # ?/? will translate in a fraction such as 4 1/3 CURRENCY – $#,##0.00 will translate in a sum of dollars with 2 decimals – $1,234.57

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. Data Validation & Name Ranges

The basics of creating a data list as well as interacting with defined names

Data validation has 3 main menus:

You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list. Data validation provides various options to restrict data inside a cell, starting from data formatting up to specific values. It can be accessed through the “DATA” Main Tab under the “Data Tools” Data validation has 3 main menus:

Settings – chose the type of data as well as the restrictions; Input Message – design the message that will appear when the cell containing data validation is selected; Error Alert – design the error message that will appear if the data inserted differs from the one we want; Data Validation

LOCATION  we will restrict this to “Brasov”, “Sibiu” or “Bucuresti”

Consider the following example: We want to fill the table below with details for each agent. However, in order to make sure everyone uses the same format, we will restrict each column to a specific type of data. We will also add specific input/error messages to let the user know more details regarding the input data LOCATION  we will restrict this to “Brasov”, “Sibiu” or “Bucuresti”

DEPARTMENT  we will restrict this data based off the Location: Brasov  UK or US Sibiu  FR Bucuresti  SP or DE or RO

DEFINED NAMES

Before going forward with our example we need to discuss Defined Names. Data validation will allow us to restrict data in the “Department” field based off the location but we will need to create specific list with each department. In order to avoid typing in the range of the list (G3:G5 for BV, G7 for SIB and G9:G11 for BUC) we can create a “Name” for each range so that we can use them with ease.

To do this, we need to access “FORMULAS” Main Tab and select “Define Name” under the “Defined Names” section DEFINED NAMES

Name  the Name by which the range will be known;

Name  the Name by which the range will be known; Scope  if multiple excel workbooks are selected, we can choose which ones will have access to this range; Comment  an explanation about the contents of a defined name; Refers to  the range which will be converted to a Defined Name

After creating 3 defined names for each of our departments, we can easily view them in the upper-left side of the screen under the drop-down menu for the cell we have currently selected. Another way to view or manage them is by clicking on “Apply Names” or “Use in Formula” under the “Defined Names” sectionC

We can then proceed to apply our data validation on both fields and customize the messages.

We can then proceed to apply our data validation on both fields and customize the messages.

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. Conditional Logic

Excel has several logical functions and this module explores some of them. We will be learning the concept of conditional logic in formulas, followed by how to conduct logic tests and use conditional operations, to our benefit

Conditional logic functions are the cornerstone of Excel Functions. Mastering nested basic functions is t

Conditional logic functions are the cornerstone of Excel Functions. Mastering nested basic functions is the first step towards mastering complicated formulas, and the most basic of nested functions is “IF”. We have discussed the IF function earlier, so let’s get into nested functions. A nested function is simply a function repeated multiple times in order to achieve a needed effect.

Let’s take a practical example. We are going to build a function that will determine what the proper bonusing is for an agent, considering that we have 3 different bonuses for each type of working hours (Full time – 1; Extended Part-Time – 0.75; Part time – 0.5;) For the formula to work, we are going to need to test the amount of sales made by each agent versus the number of sales in the bonusing system.

The first IF will have to test the working hours – Type column – while the other 3 will be related to the amount of sales, resulting in a bonus based on those targets.