FREE Excel Tips Newsletter from
Subscribe here to get more tips like this every week

Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

March 8, 2016

Hi fellow Excel Addict,

Please note the following...

Last Thursday's 'Excel in Minutes' tip told you about 'Excel's 'Secret' Function for Calculating Date Differences' (you can read it here).  After publishing that tip, I received an email from Excel Addict subscriber Steve who informed me that DATEDIF has a bug in some versions of Excel. Here is a link to an online article where Rick Rothstein (Excel MVP) discusses the issue and advises against using DATEDIF for anything important.

If you have a favourite tip that you'd like to share, or a problem that you've been trying to solve, that I could possibly include in my newsletter, please send it along to me.

I hope you enjoy the rest of your week.

Take care and keep Excelling,

Francis Hayes (The Excel Addict)

Francis Hayes ( Quote of the Day

"The present moment, if you think about it, is the only time there is.
No matter what time it is, it is always now."
-- Marianne Williamson --

If you missed my last newsletter, you can click here to view it online.

Excel in Seconds with

What the "Cannot Shift Objects Off Sheet" Message Means

When you try to insert one or more rows or columns and get a message saying "Cannot Shift Objects Off Sheet" it can be very frustrating. You're probably wondering "What does that even mean?".

Cannot Shift Objects Off Sheet_in_Microsoft_Excel_2007_2010_2013_2016_365
If you use text boxes or arrows or any other type of objects in your workbooks, it's easy to accidentally copy or move them to places where they should not be. This error message is telling you that one or more of these objects is located near the very last column (XFD) or the bottom row (1,048,576) of your worksheet. In order to insert rows or columns, Excel would have to move the object off the end of the sheet, which is impossible. Therefore, you get this error message.

The solution for this is relatively simple.

If there shouldn't be any objects in your worksheet, you just need to delete any that ARE there.

To quickly delete all objects in your worksheet...

1) Press the F5 key on your keyboard, click Special…, select Objects, click OK (all objects in sheet are selected),
Select All Objects In Sheet_in_Microsoft_Excel_2007_2010_2013_2016_365
2) Press the Delete key on your keyboard.

Maybe you DO use objects in your worksheet and don’t want to delete them all.

To delete specific objects from your worksheet...

1) From the Home tab, click Find & Select and choose Selection Pane. This will display the Selection Pane on the right of your screen listing the objects in your sheet;

Select Objects Using Selection Pane_in_Microsoft_Excel_2007_2010_2013_2016_365

2) Click the Show All button, just in case some objects have been hidden;

3) Select one of the objects in the list. That object will been selected in your sheet. You can tell by the 'selection handles' on the outside of the object;

4) At this point you can press the Tab key on your keyboard to jump from one object to another;

5) When you find the one(s) that you want to delete, press the Delete key on your keyboard;

6) Repeat the previous 3 steps until you have removed all of the unwanted objects.

Now you should be able to insert rows or columns without any fuss.

Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from
Subscribe here to get more tips like this every week

Earnings Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
 'Spreadsheets Tips From An Excel Addict' and 'Excel in Seconds' are publications of
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630