How to use microsoft excel or spreadsheets

 

The top microsoft Excel tips that every user should master.

Whether you are a Microsoft Excel beginner or an advanced user, you'll benefit from these step-by-step tutorials.

Microsoft Excel was first released in 1985, and the spreadsheet program has remained popular through the years. You can master Excel by reading these tips and tricks on how to add a drop-down list in an Excel cell to finding duplicates,how to delete blank rows in Excel, and more.

How to add a drop-down list to an Excel cell

Drop-down lists can greatly facilitate data entry. Here's a look at how to use Excel's data validation feature to create handy lists within your worksheets.

An Excel drop down list or dropdown menu can make it easier for the average Microsoft Excel user to enter data on a worksheet or workbook. Using a drop down menu in web forms, surveys, or polls can limit the entry choices for a selected cell, speeding data entry and reducing data entry error. In this Excel tip, we'll show you a quick and easy way to create a dropdown list or drop down menu using the spreadsheet application's Data Validation feature.

To create a drop down list in Excel, you need two things: A list of values (contained within a cell range) and a blank cell to use as the data entry cell.

Figure A shows a simple drop down list in an Excel sheet. To use the drop down menu shown here, someone would place the cursor over the blank, data-entry cell (E4 in this example) and click a drop down arrow to display the list of values shown in the cell range A1:A4. If a user tries to enter something that isn't an item within that list of values, Excel rejects the entry.

You can follow along with the steps in this tip by creating a new sheet with data similar to the one shown in Figure A, downloading our demonstration .xlsx and .xls files, or using your own worksheet and data.

Figure A

                              

To add the drop down list in our example to an Excel sheet, do the following:

1. Create the data validation list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D1.
2. Select cell E4. (You can position the drop down list in most any cell or even multiple cells.)
3. Choose Data Validation from the Data ribbon menu.
4. Choose List from the Allow option's drop down list. (See, they're everywhere.)
5. Click the Source control box and drag the cursor to highlight the cells A1:A4. Alternately, simply enter the reference (=$A$1:$A$4).
6. Make sure the In-cell dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won't present a drop down list.
7. Click OK.

How to create an Excel drop down list from another tab.

The data you want to use in an Excel drop down list usually won't be in the same sheet as the drop down. The good news is that identifying a list on another sheet requires only an extra click to access the sheet. We can illustrate this as follows. (You can work with your own data or download the demonstration .xlsx and .xls files.)

• If you have only one sheet, add a new one. ("Different sheet" in the demonstration file.)  
• Select E4 in the new sheet and repeat the instructions for creating a drop down from a previous Excel article through step 4.
• When you get to the step 5, where you identify the source, click inside the Source Control.
• Click the Lists sheet tab or the tab that contains your list items.
• Select the list (A1:A4).
• Click OK, which will return you to the new sheet, where you'll find a populated list in E4 (Figure A).

Figure A

How to use macros in Excel

How to use VBA procedures to generate a list of sheet names in an Excel workbook.

Whether your Microsoft Excel workbook has three sheets or 50, knowing what you have is important. Some complex applications require detailed documentation that includes sheet names. For those times when a quick look isn't good enough, and you must have a list, you have a couple of options: You can go the manual route, which would be tedious and need updating every time you made a change. Or you can run a quick VBA procedure. In this article, I'll show you easily modified code that generates a list of sheet names and hyperlinks. There are other ways to generate a list, but I prefer the VBA method because it's automated and easy to modify to suit individual requirements. 

How to enter and run code in VBA

If you're new to VBA code, you might be wondering about the terms procedure and macro. You'll see them used interchangeably when VBA is the language used. This isn't true for all languages. In VBA, both a procedure and a macro are a named set of instructions that are preformed when called. Some developers refer to a sub procedure as a macro and a function procedure as a procedure because a procedure can accept arguments. Many use the term macro for everything. Some, like me, tend to use the term procedure for everything. Furthermore, Access has a macro feature that is separate from any VBA code. Don't get too hung up on the terms. 

To enter VBA code, press Alt + F11 to open the Visual Basic Editor. In the Project Explorer to the left, choose ThisWorkbook and enter the code. If you're using a ribbon version, you must save the file as a macro-enabled file to use macros. You can also import the downloadable .cls file that contains the code. Or, you can work with either of the downloadable Excel workbooks. If you're entering the code yourself, please don't copy it from this web page. Instead, enter it manually or copy it to a text editor and then paste that code into a module.

When in the VBE, press F5 to run a procedure, but be sure to click inside the procedure you want to run. When in an Excel sheet, click the Developer tab, click Macros in the Code group, choose the procedure in the resulting dialog shown in Figure A, and then click Run.

Figure A

A simple list of sheet names is easy to generate using VBA thanks to the Worksheets collection. Listing A shows a simple For Each loop that cycles through this collection. For each sheet, the code uses the Name property to enter that name in column A, beginning at A1, in Sheet1

Listing A

Sub ListSheetNames()

'List all sheet names in column A of Sheet1.

'Update to change location of list.

Sheets("Sheet1").Activate

ActiveSheet.Cells(1, 1).Select

'Generate a list of hyperlinks to each sheet in workbook in Sheet1.

For Each sh In Worksheets

    ActiveCell = sh.Name

    ActiveCell.Offset(1, 0).Select  'Move down a row.

Next

End Sub

When adapting this code, you might want to change the location of the list; do so by changing the first two lines accordingly. The For Each loop also offers possibilities for modifying. You might want to add header names or values to number the sheet names.

This code will list hidden and very hidden sheets, which you might not want. When that's the case, you'll need to check the Visible properties xlSheetVisible and xlSheetVeryHidden. In addition, because we're actively selecting A1 on Sheet1, the cursor moves to that location. If you don't want the active cell to change, use implicit selection statements. To learn more about implicit and explicit references, read Excel tips: How to select cells and ranges efficiently using VBA.

There are many modifications you might want to make. For example, instead of an ordinary list of text, you might want a list of hyperlinks.

How to generate a list of hyperlinks in Excel

It's not unusual for a complex workbook to include a list of hyperlinks to each sheet in the workbook. The procedure you'll use, shown in Listing B, is similar to Listing A, but this code uses the Name property to create a hyperlink.

Listing B

Sub ListSheetNamesAsHyperlinks()

'Generate list of hyperlinks to each sheet in workbook in Sheet1, A1.

Sheets("Sheet1").Activate

ActiveSheet.Cells(1, 1).Select

'Generate a list of hyperlinks to each sheet in workbook in Sheet1.

For Each sh In Worksheets

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, _

        Address:="", SubAddress:="'" & sh.Name & "'" & "!A1", _

        TextToDisplay:=sh.Name

    ActiveCell.Offset(1, 0).Select 'Moves down a row

Next

End Sub

The first two lines select cell A1 in Sheet1. Update these two statements to relocate the list. The For Each loop cycles through all the sheets, using the Name property to create a hyperlink for each sheet.

The Hyperlinks.Add property in the For Each uses the form

.Add Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay]

The parameter information is listed below:

• Anchor: A Range or Shape object.
• Address: The address of the hyperlink.
• SubAddress: The subaddress of the hyperlink
• ScreenTip: Information displayed when the mouse pointer pauses over the hyperlink.
• TextToDisplay: The hyperlink text.

Our procedure's SubAddress argument

SubAddress:="'" & sh.Name & "'" & "!A1"

builds a string using the current sheet's name and the cell reference A1. For instance, if the current sheet is Sheet1, this evaluates to 'Sheet1'!A1. Subsequently, when you click this hyperlink, it takes you to cell A1 on Sheet1. As before, you can easily modify this procedure to reflect the way you want to use this list.

Both sub procedures are easy to use. Both can be easily modified to change the list's position or to add more information to the simple list. 

How to add a timestamp to an Excel record.

What's a timestamp

It's not easy to define the term timestamp because its definition depends on its use. Simply put, it's the current date and time used to identify when something happens. That might be the time the record was entered, the time a project was completed and so on. 

Use the built-in shortcut keys

If you're familiar with Excel, you probably know that you can use a couple of shortcuts to enter both the time and date:

• Date: Ctrl + ;
• Time: Ctrl + Shift + ;

If you want both values in the same cell, you can still use these shortcuts. Enter the first and without pressing Enter—while still in edit mode—enter a space and then press the second shortcut. The result is a custom format that displays the date and time. Figure A shows the result of using these shortcuts. Not only does Excel enter the date and time, but it also formats the cells for display purposes.

Figure A

There's nothing wrong with using the shortcut keys—they work, and if you don't mind the keystrokes, this is an easy solution. The downside of course is that you must remember to run the keystrokes. Combining the two into one cell probably isn't a good idea either unless your users are especially competent; they might not remember to use both, and they might be inconsistent with the delimiter that separates the two values.

I prefer the easiest solution usually, and keystrokes might work for you. On the other hand, it's probably not a great solution to pass on to users.

Why NOW() and TODAY() won't work as a timestamp in Excel

Some of you might think the NOW() and TODAY() functions will get the job done, but they won't—not easily. NOW() returns the current date and time and what it displays depends on the format. TODAY() displays the current day.

Perfect right? No. They are volatile functions. That means both functions will update every time the sheet calculates, so they're far from perfect. You could convert the results to static values, but that's a lot of work—far too much work.

How to use a macro for a timestamp in Excel

The surest way to get a correct timestamp that remains correct and consistent is to use a macro, or rather a user-defined function. The code is simple. The larger issue is when to run it. For our purposes, we'll add the macro to the Quick Access Toolbar. The user simply clicks it when ready to add the timestamp.

The sub procedure in Listing A is short and sweet. Using the selected cell—which rests in the hands of the user—this code enters a formatted NOW() function that displays the date and time. Depending on your needs, you might choose to use TODAY() or format the cell differently.

Listing A

Sub TimeStamp()

'Enter the current date and time, formatted as m/d/yyyy h:mm:ss AM/PM.

'User will select cell and then click macro button on QAT.

With Selection

    .Value = Now

    .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"

End With

End Sub

To enter the macro, do the following:

1. Press Alt + F11 to open the Visual Basic Editor.
2. In the Project Editor (to the left), select the ThisWorkbook module.
3. Enter the code into the module (Figure B). Don't paste the code from this web page into the module because it won't work. You can import the .cls file included in the download or enter the code manually. You can also paste the code into a text editor and then copy into the module—it should work. The problem is phantom web characters that get pasted along with the plain text.
4. Return to Excel and save the workbook as a macro-enabled macro. If you're using a menu version, you can skip this step.

Figure B

Let's run the procedure from the Developer tab, just to make sure it works. To do so, click the Developer tab, and do the following:

1. Click the cell where you want to enter the timestamp.
2. On the Developer tab, click Macros in the Code group.
3. In the resulting dialog, choose the procedure in the Macro name control (Figure C), and then click Run. Figure D shows the resulting timestamp.

Figure D

You don't want to run the procedure that way every time you want to enter a timestamp, so let's automate that process a bit. Specifically, we'll add the procedure to the QAT so all you must do is select the cell, click the button on the QAT and continue your work. (The menu version doesn't support the QAT, but you could assign a shortcut keystroke.)

To add the macro to the QAT, do the following:

1. Click the QAT dropdown (the arrow to the right of the QAT) and choose More Commands.
2. From the Choose Commands From dropdown, choose Macros.
3. There's only one in the demonstration workbook, but if you're working with your own file, be sure to select the correct macro: ThisWorkbook.TimeStamp.
4. Click Add to move it to the QAT list to the right (Figure E).
5. Click OK to return to the sheet. You will see the macro icon on the QAT (Figure F).

Figure E

Figure F

You're done! Select any cell and click the macro icon on the QAT to enter a timestamp. You might consider adding the user's name, change the format or enter only the date. If you're encouraged, you might create a parameter-driven procedure that lets the user choose these elements on the fly. As is, this is a solution that users won't mind using and will return consistent values.

Thanks for reading

CHEERS!

By ken