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.

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

Formula in F2 to F6:

=REPT("█",A2)&CHAR(10)&REPT("█",B2)&CHAR(10)&REPT("█",C2)&CHAR(10)&REPT("█",D2)&CHAR(10)&REPT("█",E2)

Formatting:

1. Change font size to 3.
2. Wrap Text
3. Go to Format Cells and click on Alignment Tab and Orientation to 90 Degree
4. Select the font color.

AND YOUR INCELL BAR GRAPH IS READY


To see Type 2, Click on
http://excelvbatipsforbeginners.blogspot.in/2012/08/create-in-cell-bar-chart-in-excel-2007.html

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

CREATE AN INDEX FOR YOUR WORKBOOK

Paste the below code in the module and run it


Sub create_index()
Dim i As Integer
Dim newsheet
Dim j As Integer

Set newsheet = Worksheets.Add(before:=Sheets(1))
 
   With newsheet
        .Name = "Index"

            With Range("A1:H1")
                 .Merge
                 .Value = "INDEX"
                 .HorizontalAlignment = xlCenter
                 .VerticalAlignment = xlCenter
                 .Font.Bold = True
                 .Font.Color = vbRed
                 .Font.Size = 13
            End With

j = 2
 
   For i = 2 To ThisWorkbook.Sheets.Count
          .Range("A" & j) = Sheets(i).Name
          .Range("A" & j).Select
          .Hyperlinks.Add Anchor:=Selection, _
            Address:="", SubAddress:="" & Sheets(i).Name & "!A1"

j = j + 1
 
   Next

ActiveWindow.DisplayGridlines = False

    End With

End Sub

This code will insert a sheet in workbook named "Index". This sheet will have list of name of all sheets with hyperlink. You can move to any sheet directly by clicking on it's name.

DELETE ALL SHAPES IN YOUR WORKSHEET

Paste below code in module and run it

Sub delete_shapes()

Dim i As Integer

For i = ActiveSheet.Shapes.Count To 1 Step -1
ActiveSheet.Shapes(i).Delete
Next
End Sub

CREATE DATA VALIDATION LIST WITH UNIQUE VALUES FROM A LIST OF DUPLICATES

Paste the below code in module and run it.


Option Explicit

Sub data_validation_with_unique_values()
Dim clctn As New Collection
Dim arr As Variant
Dim i As Integer
Dim distinct() As String

'Fill values from your range into array
arr = Application.Transpose(Sheet1.Cells(1, 1).CurrentRegion.Resize(, 1).Value)

'Create a list of unique
On Error Resume Next
For i = LBound(arr) To UBound(arr)
clctn.Add arr(i), arr(i)
Next i
On Error GoTo 0

ReDim distinct(1 To clctn.Count)
For i = 1 To clctn.Count
distinct(i) = clctn(i)
Next

'Paste unique values in column E
Sheet1.Cells(1, 5).Resize(clctn.Count).Value = Application.Transpose(distinct)

'Give a name range to your list in column E
Range("E1:E" & Range("E65536").End(xlUp).Row).Name = "Myrange"

'Create data validation list in active cell using named range
ActiveCell.Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=Myrange"

End Sub

Note: Change the highlighted part as per your requirement.

QUICK TIP TO REMOVE "0" FROM ACTIVE WORKSHEET

Click on Office Button

Select Excel Options

Go to Advanced and deselect the checkbox "Show a zero in cells that have zero value as shown in the below figure.(Click on the image to enlarge it)

It will remove all the zero from your worksheet.