Often times it’s useful to be able to display your worksheet’s name in a cell in the worksheet. This could be tedious if you have a file with a lot of sheets, whose names could change frequently. Luckily there are a couple ways around this problem.
If you want to display this information in a cell in your file, enter the following formula in the cell where you want the name to appear:
=MID(CELL(”filename”,A1),FIND(”]”,CELL(”filename”,A1))+1,256)
Anytime you change your sheet’s name, the formula will automatically update, so you never have to edit the name in the worksheet as well.
If you only need the information on a printout and don’t want it displayed in a cell, you can edit the sheet’s header/footer. Click View | Header and Footer | then Custom Header or Custom Footer, depending on where you want the name to appear. Determine where you want the name to appear (Left, Center, or Right) and click the worksheet button (third from the right) that looks like a worksheet with three tabs below it.
On occasion, you might find yourself creating a spreadsheet that has multiple columns all set up in a consistent format (i.e. quarterly sales figures for the past 5 years). As time goes on, you may add/remove data to the spreadsheet as needed. This may result in some columns not being used (i.e. in April only the first quarter’s information will be filled out for the current year, leaving the second, third and fourth quarters blank).
Using the quarterly sales example above, assume that you’ve taken the time to set up your spreadsheet with placeholder columns for the remainder of the year. When printing the information, you don’t want to just delete the columns, only having to recreate them in the future, and you also don’t want to have to manually hide/unhide them either.
You can use a macro to toggle between hiding and unhiding these empty columns fairly easily. The first bit of code will determine if the total in row 8 for columns B through M is 0. If it is, it will hide the column, otherwise it will make sure the column is not hidden. The second bit of code will select columns B through M and make sure they all are not hidden, so that you can toggle between hiding the unused columns, and showing all the columns.
To use this in your own spreadsheet, you may need to change a few things. 1) Change the Range reference (i.e. “B8:M8″) to whatever range you want evaluated. 2) Change “Columns” in Rng = Selection.Columns.Count to “Rows” if the range you are evaluating is a range of rows not columns. 3) If the criteria is something other than “0″, change the “0″ in If ActiveCell = 0 to whatever that criteria happens to be. And finally 4) the two lines that say ActiveCell.Offset(0, 1).Select simply moves the selected cell down 0 cells and to the right one cell. If you’re evaluating down a range of rows, change the 0 to a 1 and the 1 to a 0 so that it moves the selected cell down 1 cell and to the right 0 cells. (Note you can move up a cell by inserting a -1 as the first number and to the left by inserting a -1 as the second number).
Need help? Use our nifty guide to help figure out how to install and use your macros.
Hide Unused Columns:
Sub hide_unused()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
Range("B8:M8").Select
Rng = Selection.Columns.Count
For i = 1 To Rng
If ActiveCell = 0 Then
Selection.EntireColumn.Hidden = True
ActiveCell.Offset(0, 1).Select
Else: Selection.EntireColumn.Hidden = False
ActiveCell.Offset(0, 1).Select
End If
Next i
Application.ScreenUpdating = True
End Sub
Unhide all Columns:
Sub unhide_all()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
Columns("B:M").Select
Selection.EntireColumn.Hidden = False
Application.ScreenUpdating = True
End Sub
Sometimes, it’s useful to be able to remove personal information from your workbook, in the event that you’re sending the file to someone else, whether it’s someone from another company, posting it to a forum, or sending it via email.
Excel will, by default, save information such as the author, file title, comments, etc. This information, could be potentially damaging if it was put in the wrong hands.
Luckily, Excel 2007 makes it easy to remove this information. Click the Office Button | Prepare | Inspect Document. Check all the options on the menu that appears, and click Inspect. Excel will then check each category to see if there is any document information that can be removed and will show a Remove All button next to each that has any information. Click the button, and the information is removed.
Adding comments to your worksheet allows you to share your thoughts with other users, or to serve as a reminder for yourself.
It’s also useful to be able to add comments to a formula, so that you can let other users of your workbook know if you’ve made a change to the formula, let them know what the result of the formula represents, or share other information about the formula.
After entering your formula, type +N(”Your comment here”)
For example if you have a formula that produces a class average, you can type:
=AVERAGE(A1:A25)+N(”Period 1 Class Average”)
”For” loop is used in a macro whenever you have an action that needs to be performed a set number of times. For example, if you want to change the fill color of the first 20 cells in a list, you could have the macro select the range and simply fill as desired. But what if you wanted to only fill the cells if they were blank, for example? Telling the macro to select the whole range and fill it wouldn’t work.
This is where a “For” loop is useful. You can have the macro to go down your list, and determine if the cell is blank. If it is, then have the macro fill the cell with a color, otherwise go to the next cell, until it has run through the whole list.
The following code will do exactly what was mentioned above.
Sub FLoop()
'MACROS BY EXCELZOOM.COM
For x = 1 To 20
Cells(x, 1).Select
If Selection.Value = "" Then
With Selection.Interior
.Color = 65535
End With
End If
Next x
End Sub
- The first line identifies the range of rows to be evaluated. It also tells the macro, that if ‘x’ is less than 20, keep on going.
- The next line tells the macro to select the cell in column 1.
- The ‘If’ section determines if the selected cell is blank, and if it is, it will fill it with a color (in this case 65535=yellow), and if it isn’t blank then just move on and evaluate the rest of the cells until the end of the range.