Category Archives: Beginner

Beginner level information

Date Format Settings Excel Windows

Regional Date Formats in Excel

I recently returned from two weeks in Ireland where I installed a couple of Excel reporting systems and came across the dreaded regional date settings. My computer is set for the US region, whereas the client’s computers were set for the UK region. When I mention regional settings on my computer, I’m talking about Windows and not Excel.

Trying to get Excel to automatically switch date formats between computers using different region settings was something I experienced a few years ago while working in England. Back then I made the mistake of ustng Excel to change the formatting for my dates. Not the way to go.

Regional Date Formats

The US Short Date format is represented in the form “m/d/yyyy” which means dates are shown in a “Months/Days/Years” format. Contrast that with Ireland, the UK, and most everywhere else, where the date format is “dd/mm/yyyy” and takes the form Days/Months/Years.
Regional Date Formats

Using the Default Short Date Format in Excel

Since I live in the US my computer’s regional settings are set for the US. When formatting dates in Excel for files that may be used internationally, it’s wise to use date formats that begin with an asterisk (*) per the Format Cells Dialog box.

Date Format Settings Excel Windows

Any date formatted in this manner will display correctly in another region. I can view the date June 13, 2011 in my spreadsheet as 6/13/2011 and send it to a person using a UK regional setting on their computer and the date will automatically display as 13/6/2011.

Nice and neat. We’re all speaking the same language here.

How Not to Change a Date Format for a Different Locale (Region)

Let’s say you are in the US and are designing a spreadsheet that will be used in the UK. You want to see “what they see” and decide to change the cell formatting in a column that contains dates. You select the range, open the Format Cells dialog box (Ctrl+1) then select English (U.K.) from the Locale (Location) drop-down list, and click OK.

Excel Local Date format UK

Bad move.

First of all, look at the picture above and notice there are no date formats that have a leading asterisk (*) which means that no date format will survive a change to a different region. You are stuck with this format in every region, whether it’s relevant or not.

How to Change Region Settings in Windows

The proper way to “see” a different region’s date format is to change your computer’s Region setting. Click the Windows Start button and select Control Panel. Find and click the link for Clock, Language, and Region.

Windows Regional Settings (Navigation to)

Next click the link for Change the date, time, or number format.

Windows Control Panel Regional Format Settings

On the Region and Language dialog box click the Formats tab.

Windows Region and Language Dialog Box

In the Format drop-down box select the Region of your choice. In my case that’s English (United Kingdom).

Windows Region English UK

In the screen shot above you can see the Short Date format is dd/MM/yyyy. Click OK to set your computer’s region.

Now open Excel and all dates previously formatted with the default Short Date format for the US (*3/14/2001) will show up in the Short Date format for the UK.

To check this fact, select a cell with dates, then bring up the Format Cells dialog box (Ctrl+1) to see that the Short Date format in Excel has automatically changed to *14/3/2001.

Windows Format Cells Date UK

This is how you change the date formats for different regions.

Customize the Region Date Format in Windows

I don’t like having two digits showing for the day and month in the Short Date format. To me 4 July, 2011 should be represented as 4/7/2011 and not as 04/07/2011. However that’s what you get when you select the English (United Kingdom) region setting.

I used a custom format for the UK region short date setting to rid my spreadsheet of the unnecessary zeros. To do this you bring up the Region and Language dialog box, as we did previously, and click Additional Settings… then select the Date tab.

Customize Regional Date Format 1

Type d/M/yyy in the Short Date box and click OK.

Customize Regional Date Format 2

Now all months and days with single digits show up without a leading zero in Excel when using the short date format.

Spreadsheet Size in Excel for Windows

I’ve been thinking about converting my older XLS files to the newer XLSX format, but ran into a quirk that stopped me. Maybe I’m just too impatient. Here’s my story.

A Few Facts First

Excel versions 97-2003 (Windows) have a file extension of XLS and the worksheet size is 65,536 rows and 256 columns. In Excel 2007 and 2010 the default file extension is XLSX and the worksheet size is 1,048,576 rows and 16,384 columns.

An XLSX File with Only 65,536 Rows

I open Excel 2003, create the following spreadsheet, and save the file. The formulas are listed in my last post.

Excel 2003 Worksheet Size

Next I open this file in Excel 2010, which now is in Compatibility Mode because it’s an Excel 97-2003 Workbook (*.xls) file.

Compatibility Mode

It still has only 65,536 rows and 256 columns, which is only natural because it’s still in the older XLS file format.

XLS file in Excel 2010

I save this file to the newer XLSX format.

Save XLS file as XLSX file

But this worksheet still has only 65,536 rows.  I create a new worksheet in this XLSX file and it has only 65,536 rows.

Now I’m discouraged and stop trying to convert my old Excel files to the new file format. In fact, I tried several times over the past few years to do this file conversion, both in Excel 2007 and 2010, but had the same result each time.

The Quirk

What I didn’t realize because of my impatience and inattention is that right after you save an XLS file to the newer XLSX format, Excel is still in Compatibility Mode, which is clearly labeled at the top of the Excel window.

XLSX file in Compatibilitiy Mode

Hence the smaller worksheet size.

After you save an XLS file to the newer XLSX format, just close and re-open the file to get rid of Compatibility Mode. Then all the worksheets have 1,048,576 rows and 16,384 columns.

XLSX file not in Compatibility Mode

What is bizarre is the fact that I had to open an XLS file with Excel 2011 (Mac), to help me find quirk.

Spreadsheet Size in Excel for Mac

Most people think an Excel workbook with an XLS file extension has only 65,636 rows and 256 columns in the worksheet. They would be wrong. Of course, if you don’t use a Mac there’s no way for you to discover this fact.

When you open an Excel 97-2003 Workbook (*.xls) file in Excel 2007 or 2010 (Windows) it’s done in Compatibility Mode, which you can see at the top of the Excel window.

Compatibility Mode

I put together a few formulas to show the size of the worksheet, the Excel version, Operating System version, and System info.

XLS file in Excel 2010

This information is from an XLS file opened in Excel 2010 (Windows) in Compatibility Mode. The formulas I used are:

  • Rows =ROWS(A:A)
  • Columns =COLUMNS(1:1)
  • Last Cell Address =ADDRESS(Rows,Columns,4)
  • Excel Release =INFO(“RELEASE”)
  • OS Version =INFO(“OSVERSION”)
  • System =INFO(“SYSTEM”)

XLS Files in Excel for Mac

Excel for Mac versions 2008 and 2011 do not open XLS files in this “Compatibility Mode” and the row and column limitations do not exist. What this means is that when you open an XLS file in Excel 2011 or 2008 (Mac) you get the newer, larger spreadsheet size.

XLS Worksheet in Excel 2011

The information above is from the same file I opened in Excel 2010 (Windows). As you can see there are 1,048,576 rows and 16,384 columns in this XLS file.

How odd.

Excel for Mac Compatibility

I made a change to the worksheet by typing “test” into cell E4 and saved the file with no problem. Then I typed “test” into cell A65537, which is one row larger than the XLS format permits. When I tried to save the file, the following message appeared.

Compatibility Warning Excel for Mac

Which means I should save the file in the new XLSX format to keep my changes.

Excel for Mac has preferences for Compatibility. Choose Excel > Preferences > Compatibility.

Excel for Mac Compatibility Icon

However nothing will change the fact that if you put data outside the 65,536 row and 256 column limitation for XLS files, Excel will warn you to save the file in the new XLSX format.

It’s just another variation between Excel for Windows and Excel for Mac.

Excel Everest Icon

5 Obvious Things that 80% of People Don’t Know About Excel

This is a Guest Post by Excel Everest.

Excel Everest Icon

ExcelEverest.com | For whatever reason, most people remain unaware of these 5 ultra-simple and ultra-useful aspects of Excel.

Many people use Excel on a daily, or at least a weekly basis. But many of these people also haven’t had or taken the time to dig into even the simple workings of Excel. Through our quest to build the ultimate Excel tutorial, our company, Excel Everest, has noticed five things that you’d expect people to know about Excel, but don’t. Spend 10 minutes to learn these and you’ll already have a leg up on your coworkers!

1.) You can hide the gridlines: Yup, the gridlines that make spreadsheets look like, well, spreadhseets can be completely hidden. Why would you want to do this? Because often it makes the information you’re presenting clearer, easier to understand, and simply better looking.

Which do you like better?

Gridlines Showing

Gridlines Hidden

So, how do you do it?

Excel 2003: On the tools menu, click options. Then on the view tab, uncheck the gridlines checkbox.

Excel 2007 / 2010: Click on the view tab on the ribbon, and uncheck the gridlines checkbox.

2.) You can insert images: After you’ve inserted an image into your Excel spreadsheet and seen how easy it is and how much better and more professional it makes your spreadsheets look, you’ll be doing this all the time. There’s something about putting your company’s logo into your Excel spreadsheet that makes your work seem more professional. And it’s super easy!

Images are an easy way to make things more professional.

Image Insert

So, how do you do it?

Excel 2003: On the insert menu, go to picture, and then click “from file.”

Excel 2007 / 2010: On the insert tab, click picture.

3.) You can wrap text: When you’re typing in Excel, many people quickly complain that after they hit enter, their text either goes off to the right over the other cells, or simply disappears. When you want to type a lot of text into Excel, you need to enable the “wrap text” formatting feature. It makes managing your text in Excel much simpler.

Have you had this problem?

Wrap Text

So, how do you do it?

Excel 2003 / 2007 / 2010: Right click on the cells, hit format, click on the alignment tab, and check “wrap text”

4.) You can format cells based off the contents of the cells: Sometimes you’ll be in a situation in Excel where you want to format all cells that contain a certain value a certain way. Sure, you could go through one by one and identify them all, but with a feature called “conditional formatting,” you can create rules for Excel that will allow you to format as you’d like.

If you type “red,” the cell background will turn red.

Conditional Format Blue Vs Red

So, how do you do it?

Excel 2003: Highlight the cells you want to apply formatting to, go to the format menu, click on “conditional formatting” and then use the second drop down to set “equal to” (or use another rule).

Excel 2007 / 2010: Highlight the cells you want to apply formatting to, then on the home tab of the ribbon click the conditional formatting drop down, and under highlight cells rules, hit “equal to” (or use another rule).

5.) You can copy paste only values: Copying and pasting in Excel, if you don’t know how to only paste the values, can be a bit of a nightmare. Say you’ve got a huge set of formulas that return certain results. If you want to bring only the results of those formulas into a new spreadsheet, you’ll need to use the “paste values” feature of Microsoft Excel.

Paste Special Values

So, how do you do it?

Excel 2003 / 2007 / 2010: Copy the text with ctrl-c then paste by right clicking, hitting “paste special,” and then select the “values” button.


Excel Everest is an Excel tutorial that teaches the foundations of Excel in an interactive, engaging manner. It’s a gigantic Excel file that covers 50 topics, has hundreds of buttons and videos, and 150+ exercises which are automatically graded so you can easily see if you’ve done them correctly. The Excel Everest tutorial is for sale both to individuals and companies, and many organizations such as Google, Hymans Robertson, and PlayCore currently use Excel Everest to train their employees.

 

Table Names in Excel

Excel Tables are given default names (Table1, Table2, etc.) when they are created. Table names are similar to defined names, which makes them easy use in formulas or to select data.

There are a couple of ways to select all the data in a Table by using the Table name. 1) select the Table name from the Name box,

Name Box Both

or 2) use the keyboard shortcut Ctrl+G then select the Table name and click OK.

Go To Dialog Box

Table names can be used in formulas =ROWS(Table1) and they show up in the formula autocomplete list.

Table Name in Formula

A Table name in Excel 2010 (Windows) is distinctly visible on the Table Tools tab. Here you can easily change the Table’s defined name.

Table Name Windows

Excel 2011 (Mac) doesn’t easily reveal the Table name even though there’s a standard Table tab on the Ribbon. You have to make sure the Table is active, then choose Table > Rename, which will select the entire table and highlight the table name in the Name box. (You don’t have to actually rename the table.)

Rename Table Mac

In Excel 2010 you can also see the Table name by choosing Formulas > Name Manager.

Name Manager Windows

In Excel 2011 you choose Insert > Name > Define to see the Table name.

Define Name Mac

Knowing a Table’s name is important in Excel. It’s the first step in understanding structured Table data.

Table Design in Excel

I’m want to start using Excel Tables when building my spreadsheets. But first it’s instructive for me to explain how I think about “unofficial” tables or data tables as I like to call them.

How I Think About Tables

A data table is just a range of data, or a region of data that Excel recognizes if you were to use the keyboard shortcut Ctrl+Shift+*.

There are headings in the top row, which  I refer to as column headings or data fields. Date, Shift, Machine, and Total Hours are all examples. The Date column has dates in it, and only dates. The Total Hours field has only hours data.

Each row of the data table represents one record. For instance, think about customers. A customer record would contain name, address, city, state, zip, phone number, etc.

My Initial Table Design

The data table I want to design is for a manufacturing facility. We want to record data for each machine by shift. This is common when machine logs are used to collect transactional data throughout the shift. The machine logs are summarized at the end of each shift and it’s this data we want to input into our spreadsheet or data table.

What’s not obvious here is what data fields are collected for input. Suffice to say that the reports have already been designed for key indicators of performance and waste, the calculations traced back to their lowest common denominator or required data field, and that’s what we’re going to collect.

My Table Inputs

I started the data table in row 4 to keep the top area clear for any global input fields and controls for the user. The data fields in the header row were entered and cells formatted: bold, center, word wrap, column widths. I haven’t gone with any background colors in the header row just yet because I’m waiting to see how the Table formatting will turn out.

The header row or data fields are: Date, Shift, Machine, Number Setups, Setup Hours, Run Hours, Down Hours, Total Hours, Net Product, Waste Setup, Waste Run, Gross Product.

Initial Table Design

I’ve entered the first row of data for a machine on shift one. My next task was to format the data. I used the default Date format for the US, m/d/yyyy, centered the Shift number and Number of Setups. The Hours fields are all formatted to one decimal number. And finally the Product and Waste fields have a number formatting with comma and zero decimal.

One Reason Regular Data Tables Fail

And now the moment where an unofficial Table fails miserably. When the second record gets entered the formatting is not the same. In fact check out the data in the second row. Not good. Not the same. (The Date really bothers me.)

Table Design Fail

Convert  a Data Table to an “Official” Excel Table

Since I have the formatting set in the first row I’ll delete the second row before converting to a Table. I select a cell inside the table, then choose Insert > Table.

Create Table Dialog Box

In Excel for Mac choose Tables > New > Insert Table with Headers.

Insert Table Mac

Since Excel has recognized the correct range AND knows that my table has headers, I click OK.

Initial Official Table

Then as I’ve mentioned before, the first thing I do is remove the filter headings by choosing Data > Filter. Then I reset my column widths and re-enter the second row of data.

New Data Entered Formatting Remembered

Now comes the whole point of this article. An Excel Table remembers the formatting for each data field or column of data.

There are other things that a Table “remembers” as you extend the data, but that will have to wait until next time.

 

 

Migrating to Tables in Excel

My first epiphany with Excel was realizing how powerful it could be if you put all your data into data tables. Over the years Excel began to incorporate data tables. First in Excel 2003 as “Lists” then as official “Tables” in Excel 2007. (I’m not sure about the evolution in Excel for Mac versions.)

The reason I haven’t done more than piddle around with Tables is that common Excel users aren’t typically early adopters AND when you design data input and reporting spreadsheets you always design for the least common denominator (with regard to Excel versions.)

But I’m beginning to realize that pre-Excel 2003 worksheets are now a scarce commodity and we have lots of people using Excel 2007 or 2010 so why not start designing in some real Tables for data entry?

So I’m going on quest. To find out all I can about Tables in Excel, both good and bad. Let the chips fall where they may.

Yet this can’t be done in one post. So I’ll start now and continue with a little mini-series on Tables until I’ve satisfied my curiosity or I’m comfortable using them on a regular basis.

Right now I’ll cover a couple of things about Tables, one bad, one good.

One Bad Thing About Tables

One annoying thing about Tables is the filter handles on the heading row. I mean, filtering data is good but only when I want to filter data. Having the filter handles showing all the time obscures the headings to the point that you can’t read some of them. Case on point: the Shift heading in the picture below looks to be a different word.

Table Filter Handles

So my very first act after creating a Table is to choose Data > Filter to shut it down. Too bad this isn’t something you can toggle in advanced settings.

One Good Thing About Tables

All the cool formatting options allow you to easily change the look of any Table.

Table Formatting Styles

Nuff said, until next time.

Set the Number of Default Worksheets in Your Workbook

I opened Excel 2007 today and realized there were 3 worksheets in a new workbook. That’s better than the 16 or so Excel had in times past, but more than one worksheet in a workbook is overkill, if you ask me.

Default Worksheets in Excel Workbook

To change this travesty in Excel 2007, I’ll choose the Office Button > Excel Options. In the Excel Options dialog box the Popular tab is already highlighted, so under the section for When creating new workbooks I will change the default setting for Include this many sheets to the value 1. There. Problem solved.

Default Sheets in New Workbook Setting

In Excel for Mac choose Excel > Preferences > General then change the Sheets in new workbook to the value you most desire. Go back to 16 worksheets for all I care. Just kidding.

In Excel 2010 choose File > Options, select the General tab to get to the Include this many sheets setting.

In Excel 2003 choose Tools > Options, select the General tab to change the Sheets in new workbook setting.

View Excel Spreadsheets with the Google Docs Viewer

If you have a Gmail or Google Docs account, getting a quick look at a Microsoft Excel file is now supported with the Google Docs Viewer. Just click the View link beside the file name in your email and the Google Docs Viewer will open to give you a quick look.

Google Docs Viewer in Mail

Now you can send Excel files to people who have a Gmail or Google Docs account and they will be able to view your spreadsheet information. Not the greatest view of a spreadsheet file, but it’s better than nothing.

Google Docs Viewer with Excel File

In the Google Docs Viewer you have the option to save the file in Google Docs. The file is saved as an Excel file and you can then come back later to view the file.

There is also an option in your email to Open as a Google Spreadsheet, but this converts the Excel file to a Google Spreadsheet file and everything may not survive. Below is an example of a few Excel array formulas that did not convert.

Spreadsheet Conversion to Google Docs Formula Error

Most simple to intermediate spreadsheets — in size and complexity — will convert just fine.

Convert Coordinates for Your GPS with Excel

A few years ago I found myself just south of Paris, France one Sunday with a car and the inclination to do some sight-seeing. Since I was alone and don’t speak French my saving grace was Google Earth and a Tom Tom GPS navigation device.

Tom Tom allows GPS coordinates to be entered as a destination, but Google Earth lists those coordinates in a different format. In researching this article I found out that GPS coordinates can be presented in at least four different formats, making it difficult to understand the coordinates.

Google Maps view of the Eiffel Tower

Click the x to remove the Address box and show the interactive Google Map.


View Larger Map

Use Excel to Convert GPS Coordinates for Tom Tom

One of my destinations was to see the Eiffel Tower and Google Earth shows the GPS coordinates to be 48 degrees 51 minutes 32.64 seconds North and 2 degrees 17 minutes 34.90 seconds East. (Coordinate formatting shown as 48° 51′ 29.69″ N 2° 17′ 38.02″ E in Google Earth while holding the mouse over a position on the map.)

However the Tom Tom GPS device wanted coordinate input in degrees only, where minutes and seconds represent the fractional part. So I created a quick spreadsheet to do the conversion. (Click the picture to download the .xls file.)

GPS coordinate conversion

To convert from Degrees, Minutes, Seconds to a Tom Tom degree format, the math is:

Degrees+(Minutes/60+Seconds/3600)

This allowed me to enter GPS coordinates directly into the Tom Tom GPS device. Since I wasn’t familiar with the street names or the numbering system for locating addresses on the Tom Tom, this was much faster than trying to find the correct address in the Tom Tom street directory. The Tom Tom GPS was a loner from an associate so that was another factor in my decision to use GPS coordinates.

Note: Tom Tom allows different formats for GPS coordinates, this example just mirrored settings for the GPS I was using at the time.

Use Excel to Convert GPS Coordinates for Garmin

When I got back home to the USA and tried this with my Garmin it didn’t work because the required format for GPS coordinates was different. My Garmin GPS device wanted Degrees and Minutes only, with seconds being the fractional part of minutes. That formula is different because it requires some concatenation with an empty space between degrees and minutes.

Degrees & ” ” & Minutes+Seconds/60

So my Excel spreadsheet helped me convert numerous GPS coordinates for my trip to Paris.

I had a memorable time.

Eiffel Tower Paris

Note: If you’re wondering about the North and East designations, click this link to see how to read GPS coordinates.

Convert GPS Coordinates with Excel

Here’s an interactive worksheet that you can use to figure out some GPS coordinates for yourself.

To download the worksheet using this link.

Cross-posted at vlatte.net.

Open File As Read Only in Excel

To discourage users from making any changes to a macro file you can have the file recommend Read-Only when it’s opened.

Open as Read Only

To show this pop-up box when the file opens:

  • Choose File > Save As
  • Select Tools > General Options (on the Save As dialog box)
  • Check the box for Read-only recommended
  • Click OK
  • Name the file and click Save

General Save Options

If you select Yes to “Open as Read-only?” and try to save the file you get a pop-up screen asking you to give the workbook a new name in the Save As dialog box.

Read Only Save As

Of course, selecting No to “Open as Read-only?” will give any user the the ability to make changes to the file.

This is one Excel option to use to avoid unnecessary changes to any file.

My First Excel 2011 Chart

Right now I’m on the Ski slope and this particular chart served to help me kill two birds with one stone. I needed to show my daughter which ski pass would be most beneficial to her wallet and produce a chart for this article.

Charts should speak for themselves, loud and clear. This one compares the cumulative cost for an Advantage Pass and a Standard lift ticket. The Pass is $69 and your first lift ticket is free, then you get $12 off each weekday lift ticket. A standard lift ticket is $60 each weekday.

Stevens Pass Break Even

And while I started out using Excel 2010 (Windows) the formatting seemed easier in Excel 2011 (Mac) so that’s what I suffered through.

You can see the break even point is on day 2 when you have saved a whopping $3 over the standard lift ticket.  Below the chart is the source data with a Sparkline chart for the Total Savings.

Of course I had to put in a picture of the mountain where I’m happily sliding down, repeatedly, on this fine day. And to my daughter who loves skiing just as much as I do, Happy Birthday!

The Mini Toolbar in Excel

The Mini Toolbar is nice to have when you need to do basic formatting and the Home tab is not displayed. Introduced in Excel 2007 and available in Excel 2010, the Mini Toolbar appears above the shortcut menu when you right-click a cell, range, chart, shape or other object.

Note: There is no Mini Toolbar in Mac versions of Excel (2011, 2008).

The contents of the Mini Toolbar are dependent on the object you right-click.

Right-Click a Range

I frequently Bold and Center format my table headings, but when the Home tab is not displayed it’s quicker to use the Mini Toolbar.

Mini Toolbar Range Option

Right-Click a Chart

The Mini Toolbar has a drop-down list so you can pick the chart element you need to format. Below I’m changing the Plot background color.

Mini Toolbar Chart Options

Right-Click a Shape

There’s a lot of stuff you can format on a Shape object from the Mini Toolbar. Below I change the shape background fill color, change the font color, change the font to different sizes, then bold and center the text.

Mini Toolbar Shape Option

Quickly Format Something with the Mini Toolbar

Start playing around with the Mini Toolbar and you’ll find that it’s a time-saver. Especially when the formatting you need – Home tab, Chart Tools tab, Drawing Tools tab – isn’t currently selected.

F2 in Excel for Mac

Switching to a Mac has its advantages but Excel shortcuts isn’t one of them. The main gripe I have is that keyboard shortcuts should be the same in every version of Excel, yet the Windows and Mac worlds are light-years apart. The F2 key is a fundamental shortcut I learned early in my Excel life. It edits the active cell and positions the cursor at the end of the cell contents.

You want to edit a formula, hit the F2 key. But wait! In Excel for Mac the F2 key cuts text from the active cell. How could they do this? What were they thinking? Window users are screwed when it comes to keyboard shortcuts in Excel for Mac. Remember that well.

Edit the Active Cell in Excel for Mac

CONTROL+U is the new F2 when you’re using Excel with a Mac. If you want to edit the active cell use the keyboard shortcut CONTROL+U.

Edit Cell Shortcut Excel for Mac

If you use F2 to edit a cell on a Mac, remember your screwed, and think FU. Then take control of the situation and use CONTROL+U to edit that cell and get on with your life.

Add Freeze Sheet Panes to the Quick Access Toolbar

Add a one-click Freeze Panes command to the Quick Access Toolbar in Excel 2010 and 2007. This command icon is conveniently hidden from the Ribbon and is named Freeze Sheet Panes.

In a previous post I made the recommendation to add the Freeze Panes command to the Excel Quick Access Toolbar, but recently found out that this is a sub-optimal solution because with the Freeze Panes command this requires two clicks.

One click activates a drop-down list, then you have a choice for your second click: Freeze Panes, Freeze Top Row, and Freeze First Column. The only one of interest to me is Freeze Panes. So why do I have to click twice?

Freeze Panes Command Icon

As it turns out, I don’t. The Freeze Sheet Panes command works with just one click. Here’s how to add it to the Quick Access Toolbar.

  • Right-click the Ribbon
  • Select Customize Quick Access Toolbar…
  • In the Choose Commands from: drop-down list select Commands Not in the Ribbon
  • Click inside the left-pane and press the key G (faster than scrolling)
  • Select Freeze Sheet Panes
  • Click Add
  • Position the Freeze Sheet Panes command in the right-pane
  • Click OK

Freeze Sheet Panes Command Icon

When you are ready to Freeze Panes in your worksheet, click the Freeze Sheet Panes icon.  Any cell above and to the left of the active cell is frozen.

In my opinion Excel should have made this a primary command icon when they created the Ribbon in Excel 2007. Instead it’s hidden from the Ribbon. What were they thinking?

Related Posts Plugin for WordPress, Blogger...