CREATE IN-CELL BAR CHART IN EXCEL 2007 USING FORMULA- TYPE 2

Enter formula in C4  =REPT("|",B4) and drag it down


Reduce font size to 7 and choose any colour and your chart is ready.

To See Type 1, Click on
http://excelvbatipsforbeginners.blogspot.in/2012/07/create-in-cell-bar-chart-in-excel-2007.html

Did it help you? Please post your valuable comment. Thanks!!

SPLIT CELL CHARACTERS USING VBA

Paste the below code in the module, select the cell you want to split and run the code.


Sub split_cell_char()
Dim i As Integer
For i = 1 To Len(ActiveCell)
ActiveCell.Offset(0, i) = VBA.Mid(ActiveCell, i, 1)
Next
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Did it help you? Please post your valuable comment. Thanks!!

SUM ALL DIGITS OF A CELL USING VBA

Paste the below UDF(User Defined Function) in module and use function.

Function Name  "=sum_cell_values"

Function sum_cell_values(rng As Range)

Dim i As Integer

j = 0

For i = 1 To Len(rng)

j = WorksheetFunction.Sum(j, VBA.Mid(rng, i, 1))

Next

sum_cell_values = j

End Function

Did it help you? Please post your valuable comment. Thanks!

SUM UNIQUE VALUES IN A RANGE

Array formula in B2:

=SUM(IF(FREQUENCY($A$2:$A$10,$A$2:$A$10),$A$2:$A$10))


with CSE

Is this post helpful to you? Please post your valuable comment. Thanks!

CREATE DYNAMIC DATA VALIDATION LIST

Say your data is in the range A2:A6 and you want to create a data validation list using this data  in C1. It is easy to do. You will go to Data-- Data Validation--List--Select Range--Press OK and you are done.

But sometimes we want our data validation list to get updated automatically when we add entries in our data.
For this we need to create a dynamic named range for our data. To do this go to Formula Tab--Click on Define Name. Below window will appear.

Enter name whatever name you want in the NAME field (I have named it as MyRange) and enter the formula as shown in the figure in refers to field and press OK

Formula :  =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

Select cell C1(where you want to create data validation list) and Go to Data Tab--Click on Data Validation. Below window will appear

Select List in the combobox under Allow option and enter the name (=MyRange) that you have given in the previous step and press OK.

Now whenever you add an entry in you data, your list will automatically get updated. I have add few names and my list got updated. See image below

Is this post helpful to you?

Please post your valuable comment. Thanks!

HOW TO INSERT CAMERA IN QUICK ACCESS TOOLBAR

Below are the steps

1. Click on MS Office button

2. Click on Excel Option

3. Go to Customize

4. Select All Commands

5. Click on Camera and then Add

6. Click OK
(Click to Enlarge)

CONCATENATE YOUR TEXT WITH FORMAT USING EXCEL

STEPS:

1. Enter your text in A1,B1 and C1
2. Adjust column width so that combined width of A1:C1 is equal to the width of E1
3. Select range A1:C1
4. Click on the Camera Tool

5. Select E1 and done.

Now whenever you change the format of any cell (A1,B1 or C1), format of the text in E1 will change automatically.

To know how to insert Camera in Quick Access Toolbar. Click on
How to insert Camera


Is this post helpful to you? Please post your valuable comment.