Categories
Excel

How to STDEV on different ranges using Excel formulas?

Consider an Excel file in which we have different group of numbers and we want to calculate STDEV for each rang using a formula. The process is as follow,

Group of numbers in an Excel sheet

 

=IF(A1="",STDEVA(INDIRECT(CELL("address",A2)&":A"&SMALL(IF(A2:A200="",ROW(A2:A200),""),1)-1)),"")

Note: This is an array formula and should be inserted with Ctrl+Shift+Enter
STDEV (Sample Excel File)

Categories
Excel

Inserting blank rows between the rows of a database

This blog post is about inserting rows between the rows of a database. Selecting rows one by one and insert row is very exhausting especially when there are so many rows to do so. A better solution is as follow,

Consider the database shown in the image below.

A sample Database in an Excel Spreadsheet

Step 1:

Insert a column to the left of the database and type numbers from 1 to the last row number of database-1. (here we have data up to row 16, so I will type number from 1 to 15)

Inserting a column to the database

 

Step 2:

Now copy the group of numbers under each other, how many times? Depends on the number of blank row you want. For example, I want to insert 2 blank rows between each row. So I copy and paste these numbers twice under each other:

Inserting group of numbers

Step 3:

Now select whole database and go to Data>sort and do as shown in below figure:

Data & Sort Database

After clicking OK you will have 2 blank rows between your database rows. Then you can delete the helper column (column A)

Database Blank Rows

And after deleting the helper column (column A) we will have this:

After deleting the database helper column

Additionally, for inserting a column between columns in a database, do similar steps above and set the option in sort to left to right, then do as shown above.

Inserting column between columns in a database

\\

Categories
Excel

Connecting a Shape or WordArt to an Excel Cell

For connecting a shape to a cell in an Excel file, follow these steps.

Step 1

Insert a word art or shape

A sample WordArt in Excel

step 2:

Delete the text in the word art and type ‘=’ in the Excel formula bar. Then click a cell.

Placing a WordArt in an Excel Spreadsheet

step 3:

Now whatever you type in the cell, you will see it in shape or word art

bSuperior System as an Excel WordArt

 

As you see, it’s bSuperior System!

Categories
Excel

An example of Paste Special – Adding three zeros to numbers on Excel spreadsheet

Consider a column of Excel that contains cost values in terms of thousands of dollars but without zeroes like the image below.

A cost column in excel containing numbers in terms of thousands

How to convert these values to actual numbers? Like how to convert 13 to 13,000?

Without any Excel formula, you can multiple 1000 to all your costs with paste special. For this go as steps:

Step 1:

Type 1000 in a cell and copy. (Ctrl+c)

Copying in the cost column of the Excel file.

Step 2:

Select all your data (numbers) and go to Paste special (Ctrl+Alt+V). then do as the image below:

Doing paste special on the Excel cost column

At last you will have all the data with three zeros at the end.

A sample column of cost in an Excel file

Note:

This is not an Excel formula. If you want to undo this operation you should do as the same process but select Divide.

Categories
Excel

Making Organization Chart with Excel & Visio

Here we discuses about making Organization chart with visio by importing data from Excel sheet.

It is very simple and there is no need to use SmartArts or Shapes.

Step 1:

Prepare database in excel in this way:

The database sheet must have 2 columns. The first column is the person who reports and is labeled as “Owner”.  Second column is the person who gets the report and is labeled as “Report to” (See the figure below). Person A is the head of organization. B1 -B6 reports to A and so on.

figure 1

step 2:

Open Visio> open Organization template> select “Org Chart” tab> click Import. you will see’ll something like the figure below.

Note: the other steps will shown in figures step by step.

figure1

2

3

4

5

6

7

after clicking finish you will see the Organization chart.

8

Sample files can be downloaded from here:

Sampl File