Did you ever feel like you were never fast enough with your finding commands in Excel? This is your chance to improve by using keyboard shortcuts. I wrote this book, No-Mouse Microsoft Excel, as part of a series, The No-Mouse guides. It’s a Kindle book, which means you can read it on your Kindle device or on your computer or Android tablet.
The book usually costs $2.99, but if you take action now, you can have it for free. More precisely, the book will be free starting March 15th until March 19th, thanks to Amazon KDP which allows authors and publishers to organize such promotions once every 90 days. If you want it, click here to get it now without paying a dime. This is probably the last time it will be available for free, so hurry up!
I’d appreciate your honest review on Amazon. I’m going to use all advice I get to release an improved second edition soon.
Did you notice my Ask a Question section? This is the first question coming from one of my readers:
Why are my Insert Illustrations options disabled on the ribbon in Excel 2010?
I checked my Excel 2012 version and guess what? I had no possibility to insert any illustrations, pictures or charts in my Excel worksheets either. The fix is very simple and it works for both Excel 2010 and 2012. I didn’t check it for Excel 2007, but if you did, please let us know if it works.
If your ribbon looks like this, you won’t be able to draw a single line or insert any graphic object in your worksheet. Don’t worry, there’s nothing wrong with your software, it’s only a matter of settings.
- Go to File on the top menu
- From the left side menu, choose Options (it’s the last item on the list)
- Go to Advanced on the left side menu
- Scroll down until you see “Display options for this workbook” in the main area of the screen
- Where it says “For objects, show” you need to enable “All” instead of “None”
- Click OK and you’re done
Advanced options for the ribbon settings in Excel 2012. From here you can change lots of options in order to configure your Excel just the way you need it.
Did this happen to you as well? Did you solve it before reading my article?
Yesterday I tried recording a macro in Excel 2010. As I skipped using Excel 2007, my leap from Office 2003 to 2010 meant that I needed quite a while to work my way around the new menus. I don’t know about other heavy users of Excel 2003, but when I first had to use the ribbon, I found it very frustrating. Anyway, now that I “made peace” with the ribbon, I’m happy to share my best findings with you.
Macro commands were hard to find. The reason is that Excel 2010 doesn’t display them by default. You need to customize the ribbon to have them enabled.
This is how the default Excel 2010 ribbon looks:
You can see that there are nine tabs: File, Home, Insert, Page Layout, Formulas, Data, Review, View and Acrobat. At the first glance, I would have thought that the Macro commands should be either under the Data tab or under the Formulas one. I was wrong. They belong to the Developer tab, which is not active by default. In order to activate it, go to File, then choose Options from the left side menu, just under Help.
From the next screen, choose Customize Ribbon. If you look to the right side of the screen, you’ll notice a list of main tabs. The Developer tab is included, but it is not activated. If you tick its corresponding box and click OK, you’ll activate it, so it becomes visible after you return to your worksheet.
Click on the Developer tab. Your macro commands are all in the first group of icons on the ribbon.
From this point on, things are as you knew them from Excel 2003. If you’re new to macros, please read further to see how you can use them.
What are macros and how to make them?
A macro is a sequence of commands which is stored in a Microsoft Visual Basic module and which can be accessed each time you need to perform those commands.
The easiest way to create a macro is to record it. For this, you have to press the record button, give your macro a name, a shortcut key and a description, then perform your sequence of commands.
If you do what I just did in the previous image, you’ll get an error message due to the fact that the macro name can’t contain spaces:
You can use underscores instead of spaces, if you need to. When there are no more errors, you’ll see that the previous menu just disappears. Although you might think nothing happened, beware: your macro is currently being recorded. This means that all your actions are stored. Perform your sequence of commands as you want them to be. If you make a mistake, you can always start again, but with a bit of care and planning, you’ll manage to get your macro recorder properly from the first attempt.
When done, stop the recording as indicated in the screen capture above.
After recording it, you can run or edit your macro. Editing is needed in case you made a mistake during the recording and you want to correct it in Visual Basic, so you don’t have to record the whole sequence again. Another reason for editing can be to extend the functionality of your macro. For instance, if I record myself selecting a table, the information stored will be that I selected a certain area. If I want to use the macro on a table that has different number of cells, my macro won’t select it properly. By editing it, I can modify the Visual Basic command in such a way that the information stored will be “select the entire table, no matter how big it is”. This is something you can’t achieve by simply recording your actions. I’ll soon publish a detailed tutorial on how to edit a macro. Subscribe to ExcelTuts.net by email to get notified in your inbox when a new article is published.
When I switched from the old good Excel 2003 to Excel 2010 Starter edition, I had a sad moment trying to figure out how to freeze panes. I quickly discovered that’s not possible. Since it’s one of the commands I love and thoroughly use, I got convinced to make the switch to the full version of Excel 2010.
When is FREEZE PANES a must-have?
If you’re only an occasional Excel user and you only handle small lists and tables, you don’t need freeze panes at all. This command becomes handy when you have big tables, which exceed the screen area, so you need to frequently scroll down or to the right. Let’s say you have this huge table of daily exchange rates of currencies for year 2012:
This is what happens when you scroll:
The table header and the leftmost column containing dates become invisible, making it very hard to figure out the meaning of each cell in the table. You have no idea which date and which currency correspond to any of the cells. If you need to work with such volumes of information, you need a way to make those headers stick to the visible area, otherwise each little task will take you ages.
By using Freeze Panes, you can freeze a number of rows and columns, so they are always visible. The rest of the table is scrolling underneath these rows and columns. Now you know where you are, no matter how far you scroll down or to the right. This is how the same worksheet as above looks after freezing the top row and the left column:
For freezing your rows and columns in Excel 2010, you need to do the following:
- Position your cursor right under the header row and in the next cell to the right of the first column (because we want to keep the first row and the first column on the screen)
- Click on the View tab on the ribbon, to activate it. You’ll notice the ribbon icons change accordingly
- In the Window section of the ribbon, click on Freeze Panes, then choose Freeze Panes from the drop down menu that appears
That’s all. If you want to unfreeze panes, follow again the above steps. The menu item which was previously “Freeze Panes” becomes now “Unfreeze Panes”. Click on it and you’re done. It doesn’t matter where your cursor is when you do this.
Special note for Excel 2010 Starter users: if you have a worksheet which already has Freeze Panes applied to it and you open it in Excel 2010 Starter, it will preserve the freezing as it is. You can use it, but you can’t get rid of it, nor you can change its position.
How to find the FREEZE PANES command faster
The Quick Access Toolbar is one extremely handy tool which enables you fast access to whatever commands you need to use more often. Tomorrow’s tutorial on ExcelTuts.net will be about adding Freeze Panes to the Quick Access Toolbar.
There would be a few reasons why you would want to protect your Excel worksheets. Prevention of accidental delete of formulas by either yourself or other users is probably the most common.
A protected worksheet can be viewed by anybody, but it can’t be edited unless you change its status to unprotected again. It is also possible that only certain cells are protected, allowing users to enter their own data in the unlocked cells. Big companies would use such Excel files for their financial planning. They would send such templates to employees, asking them to fill in data, then return the files. Imagine how easy it is for a beginner Excel user to delete cell content such as formulas, without even knowing it.
Protecting the whole worksheet
This is how to do it in Excel 2010:
You need to have the home tab active on the ribbon. If you don’t, just click on it. On the ribbon, you’ll notice a group of icons named Cells.
Click on Format. A drop-down menu will open.
Click on Protect Sheet.
Enter your desired password and click OK. You’ll be then prompted to enter it once more, in order to make sure you didn’t have any typos. Enter it again and click OK.
Now your worksheet is protected. If you try to double click any cell for editing it, you’ll see that’s not possible. If you look at the screen capture above, you can notice that there’s a list of actions you can allow your users to perform on a protected sheet. The first two, Select locked cells and Select unlocked cells are enabled by default. If you want to give more freedom to your users, you can enable other options by clicking their tick-boxes.
Protecting only certain cells in the worksheet
By default, all cells become locked when you activate the worksheet protection. If you want some cells to remain editable even after protecting the sheet, you need to unlock them.
First of all, select all cells which you want to remove protection from.
With the Home tab active and the desired cells selected, go to the “cells” tab and click on Format.
On the drop-down menu, you’ll notice an item called “Lock cell”, with a little padlock icon next to it. The icon is surrounded by a yellow border, which means the cells are currently in the “locked” state. Clicking on Lock Cell will unlock them. For your curiosity, if you access the same menu again, you’ll see that the padlock icon has no border. This means the current state of the selected cells is “unlocked”.
Next, protect your worksheet again as described before. Try to enter data in the unlocked cells, then in the protected ones. You’ll see it works.