Ozgrid Excel Newsletter. Excel Newsletter Archives

Excel Tips | EXCEL VBA TIPS

NEW EXCEL VIDEO TRAINING-HALF PRICE FOR SHORT TIME!

Quick & Dirty Tips For Microsoft Excel

Formula Quick Tips

Rather than typing those mega long formulas in the result file, that reference outside Workbooks, type it in the "outside Workbook" then Cut and paste to the "result file"

Copy formulas from one Workbook to another. Open both Workbooks and select the formula range. Now, CUT, not copy, the formulas and paste into the other. IMPORTANT: close the formula Workbook you CUT from WITHOUT SAVING.

Toggle through Relative and Absolute Range reference. Select the cell and then, in the formula bar, click the range reference and push F4 until the reference type you need is seen.

Create Custom Lists

The most flexible way to create a custom list is to enter the list contents into a range of cells. For example, say you have a list of 10 employee names. Enter each name, starting with cell A1 and ending with cell A10, and sort the list, if needed. Now go to Tools>Options>Custom Lists (*Excel 2007) and click the collapse tool to the left of the Import button. Using the mouse pointer, left-click in cell A1 and drag all the way down to A10. Click the Collapse tool again, then click the Import button, then OK. From this point on, the custom list will be available to all workbooks on the same computer.

*Click the Microsoft Office Button, and then click Excel Options. Click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.

Custom Lists Quick Tips\

Once you create a custom list, you can turn the list upside down. To do this, return to the column next to the custom list and place the last entry from the list in the top cell. In the cell beneath it, place the second-to-last entry. Select both cells and double-click the fill handle. The list you produced should be reversed

Create a custom list of the Alphabet. Enter =CHAR(98-ROW()) in A1 and Fill Down. Copy the Alphabet list and Paste Special - Values. For upper case letters, use =CHAR(ROW()+64)

Special Characters

Very similar to the above with the use of the CHAR Function, but you use =CHAR(ROW()) in A1 and then Fill Down until you get the #VALUE! Error. Now copy the list of special characters and Paste Special - Values.

Remove Special Characters

Often, when you copy or import data into Excel, you end up with some special characters that are not wanted. Sometimes, you cannot even see them, but they cause grief when they are referenced by formulae. For the ones you can see, select the cell and then from the Formula Bar, highlighted the special character and copy (Ctrl+C). Now show Find & Replace (Ctrl+H) and in the Find What box paste the special character (Ctrl+V), and leave the Replace With box blank. Now click Replace All. The same applies to the special characters you cannot see, but as you cannot see them you highlight, what appears to be a space, from the Formula Bar.

Transpose That Formula Range Without Altering The Range References

Select the formula range and show Find & Replace (Ctrl+H). In the Find What box type = and in the Replace With box type # or any non common character that does NOT appear in any of your formulas. Now copy, what were formulas, and Paste Special - Transpose. Now simply select the transposed range and reverse the Find & Replace you used above.

Excel VBA Macro Codes

Quick Access to Excel Custom Lists. See Also: Adding a Command Button to the Excel Right Click Menu

Excel Custom Lists are a great way to quickly get a list of numbers or text onto a Worksheet. These are done via the Excel Fill Handle . Excel has built in Lists for Weekdays (Mon-Fri), Months (Jan-Dec) and numeric sequences. We are also able to add our own Custom Lists via Tools>Options - Custom Lists. However, once you have added a few of your own Custom Lists, it can be hard to remember the first item in the list that must be entered in a cell. This is necessary and then you can drag down via the Excel Fill Handle .

Code For Quick Access to Excel Custom Lists

The code below must be added to the Private Module of the Workbook Object (ThisWorkbook). To get there quickly, while in Excel proper right click on the Excel icon, top left next to File and choose View Code. It is in here the code immediately below must be placed.

Private Sub Workbook_SheetBeforeRightClick _

(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)



Dim cBut As CommandBarButton

Dim cControl As CommandBarControl

Dim lListCount As Long

Dim lCount As Long

Dim MyList

Dim strName As String



    On Error Resume Next

        With Application



            '' Uncomment below to reset back to default.

            ' .CommandBars("Cell").Reset



            For Each cControl In .CommandBars("Cell").Controls

                If cControl.Caption Like "*....*" Then cControl.Delete

            Next cControl



            lListCount = .CustomListCount



            For lCount = 1 To lListCount

                MyList = .GetCustomListContents(lCount)

                Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)

                    With cBut

                        If UBound(MyList) = lListCount Then

                            .Caption = MyList(1) & "...." & MyList(UBound(MyList) - 1)

                        Else

                            .Caption = MyList(1) & "...." & MyList(UBound(MyList))

                        End If

                            .Style = msoButtonCaption

                            .OnAction = "AddFirstList"

                    End With

            Next lCount



        End With



    On Error GoTo 0



End Sub

Then, add the code below to any standard public Module (Insert>Module)

Sub AddFirstList()

Dim strList As String

    strList = Application.CommandBars.ActionControl.Caption

    If Not strList Like "*...*" Then Exit Sub

    ActiveCell = Left(strList, InStr(1, strList, ".", vbTextCompare) - 1)

End Sub

Now, each time you right click on a cell you will see the first...last items in each Custom List. Then, when you click it, it places the first Custom List item into the active cell. Then you simply drag down via the Excel Fill Handle to get the rest of the list.

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft