Contextures

Contextures News

Excel Navigation - No Macros

Mar 15, 2016

Go to Next and Previous sheets without macros, troubleshooting, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.

 -  Debra -  ddalgleish @ contextures.com
     Visit my Excel website for many more tutorials and videos.

Sheet Navigation No Macros

In some large Excel workbooks, it's helpful to get to the previous sheet or the next sheet quickly. You can do that with worksheet buttons and macros, but sometimes you don't want to add those, especially if there aren't any other macros in the file.

I've created a new sample file that you can download, and it uses hyperlinks to go to the next and previous sheets. The hyperlinks are created with the HYPERLINK function. You just have to create a list of the sheets that you want to navigate to, and name that list. Then, copy the formulas to each sheet, and click the links to move from one sheet to another.

To get the sample file, go to the Excel Sample files page on my website. In the Functions section, look for FN0044 - Hyperlink Formulas for Worksheet Navigation

Troubleshoot Large Files

Last week I discovered that one of my sample files was way bigger than it should have been. Nothing looked unusual, so I used my Excel Tools add-in to create a list of the sheets, and show the used range on each sheet. That quickly showed the problem -- one sheet had 15.7 million cells in its used range! To fix it, I deleted those rows, and saved the file.

If you don't have a copy of my Excel Tools add-in, there is a macro on my blog that you can copy to your Personal workbook. Then, run it, to see a list of sheet for the active workbook.

Your Feedback

Thanks for sending your comments and questions each week, after you read my newsletter. I love to hear from you!

Last week, I heard from Ron L, who reminded me of the handy "Day Zero" trick that you can use with the DATE function. If you use zero as the day number, Excel returns the last day of the previous month. Here's the example that Ron sent:

=IF(DAY(DATE(2016,3,0))=29,"LeapYear","Not LeapYear")

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Excel Skills -- Brad Edgar has a great resource list, with 27 ways to improve your Excel skills. Is there somthing in that list that you haven't used yet? (Level - All)

Data Insights -- You can register to watch free online sessions, live-streaming from Microsoft's Data Insights Summit, on March 22-23. I couldn't make it to the conference, so this will be the next best thing. (Level - Intermediate/Advanced)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.

Fun With Excel

It's March Break here, so the kids are out of school for a week, and I'll have some little visitors. I like to plan ahead, so I made a bingo game in Excel, that uses pictures instead of numbers. I'll print out a few cards, and we can play a few games if we need to relax for a few minutes.

If you're entertaining kids too, you can download the file from my Sample Excel Files page -- go to the Functions section, and look for FN0045 - Excel Bingo Card With Pictures.

I used the Webdings font to create the pictures, so I hope the kids recognize what all of them are! I left out some of the really ancient pictures, like the film projector, phone modem and fax machine shown below.

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

www.contextures.com/newsletter/excelnews2016/20160322ctx.html

Recommended Excel Products

  • Pivot Table Tools: Save time and frustration when working with Excel Pivot Tables. My Pivot Power Premium add-in saves your preferred settings, so you can apply them all with a single click. Also, quickly change all or selected fields to Sum, change the number formatting, and remove "Sum of" from all the headings. And many more time-saving feaatures!

________________________

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

 

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:47 PM