Drop down lists

  • Under Allow: from the drop down list select List.
  • In the Source field that is now displayed key in the values that you would like to have in your list separated by a comma.
  • When complete, Click O.K.
  • The cell will appear with a list arrow. Click that arrow will display the values entered.

How do I add a comma to a column of data?

Youtube How to add a comma to a column of data

To add a character to existing data: for example to add a comma to the end of a number the use of the & character
  • Select an adjacent cell and key in a =
  • Select the cell that contains the number
  • Key in & and then " followed by the comma ,
  • To complete the formula key in a final " and press enter
To carry out for the whole column, select the completed cell and double click the fill handle. The formula will fill to the end of the data or it comes to a blank cell

Transpose

View a video with instructions how to use Transpose

  • Transpose is a method of moving horizontal cells to vertical cells or move vertical cells to horizontal
  • There are a few tricks to make it work properly
  • Going from horizontal to Vertical
  • If you moving four horizontal cells, select four vertical cells to receive the data

  • in the formula bar enter =TRANSPOSE($A$1:$D$1)
  • the cells that we are using are in A1, B1, C1 and D1

  • Note in the formula the cells have an absolute reference;
  • Click on A1 and D1 in the formula bar and click the F4 buttton on your key board to place the $ around the values
  • This is an array, therefore the final step is to click on the formula in the formula bar and press ctrl - shift -enter which will install curly brackets around your formula

Clean Trim

  • Trim and Clean are particulary useful when you download data from other systems.
  • Trim removes spaces from the beginning and end of data.
  • Clean removes non printing characters from in between the data.
  • They can each be used separatly but they work well together.

=TRIM(CLEAN(...))

  • Using the formula, we are able to remove spaces from K5

What is the VBA code for Adding a line immediately before footer text.

  • This code is written for Excel version 2013 
		Sub AddFooter()
'
'          Adds Filename, Worksheet Name and Date (static or updated type) as Footer
'
           Dim Response As Integer
           Application.ScreenUpdating = False
     	   With ActiveSheet.PageSetup
           		.LeftFooter = "________________________________" & Chr(10) & "&8&F"
           		.CenterFooter = "_________________________________" & Chr(10) & "&8&A"
           Response = MsgBox("Do you want to maintain today's date as static ? ", vbYesNo _+ vbQuestion)
           If Response = vbYes Then
                .RightFooter = "_________________________________" & Chr(10) & "&8 " & Format(Now, "dd/mm/yy")
           Else
           	    .RightFooter = "_________________________________" & Chr(10) & "&8" & "&D"
          
            End If
            End With
   
           Application.ScreenUpdating = True
   
           End Sub
  • This code results in the following

Unlocking Cells in a Protected Worksheet

Unlock Cells in a Protected Sheet

  • At times it is desirable to leave certain cells open when you when you password protect a sheet.
  • For example, if users are allowed to enter values without touching your formulas, this method would be ideal.
  • Prior to protecting your work sheet, select the cells that you would like to remain unlocked.
  • On each of the cells right click and Select Format Cells

  • From the resulting dialog box select the protection tab
  • Click to the Locked box so that it not checked.

  • Click O.K.
  • Continue and protect the sheet by Select the Review Tab
  • Click Protect and enter a password

  • The worksheet will be protected except for the unlocked cells