Category Archives: Beginner

Beginner level information

The 20 Most Useful Excel Shortcuts to Utilize

Reasons to Use Shortcuts

Excel has many uses, although creating spreadsheets is probably its most common one. Within a spreadsheet, both written and numerical data is often used, so learning some of the shortcuts for both types of data can help save time when trying to organize information.

Image via Keyshorts

Using shortcuts takes less time because you won’t need to use the mouse to go to the ribbon to find the function or formula that you need. Also, by memorizing the shortcuts, your work will be more precise. You will be less likely to make mistakes that will need to be corrected by yourself or someone else.

Creating spreadsheets can be a tedious job, especially if there are large amounts of data to organize. However, by learning Excel shortcuts, the task can become easier because it will be quicker to do, and your accuracy will improve as well.

Here are 20 Excel shortcuts for Windows and Mac that can help you work more efficiently.

Workbook Operations

When you’re ready to start a new worksheet or continue working on one, these shortcuts can help open a current workbook or a new one and save it when you’re done finished entering information on it.

Open Workbook: Ctrl + O (Windows and Mac)

This shortcut helps you open a workbook on which you’ve been working. Once you press the keys, the recent workbooks box will appear, and you can choose the one you need.

New Workbook: Ctrl + N (Windows and Mac)

If you need to open a new workbook, this shortcut works for both the Windows and Mac operating systems.

New Worksheet: Shift + F11 (Windows and Mac)

This shortcut adds a new worksheet to a workbook.

Save As: F12 (Windows) ⌘+ Shift + S (Mac)

The F12 function key helps you save the worksheet or workbook on Windows. The combination of the keys shown above will do that on a Mac computer. After the keys are pressed, the dialog box opens so you can insert the name of the workbook.

Close Excel: ALT + F4 (Windows) Ctrl + Q (Mac)

When you’re finished with Excel, this shortcut closes the program, not just the workbook.

Ribbon Operations

If you’re new to Excel, you may not be familiar with the ribbon. The ribbon is the box above the worksheet that displays the tabs and buttons for the various commands on the application.

Show or Hide Ribbon: Ctrl + F1 (Windows) ⌘ + OPT + R (Mac)

Use this shortcut to open or hide the ribbon box.

Show Ribbon Accelerator Keys: Alt (Windows) n/a (Mac)

The accelerator keys are other shortcuts on Excel. When you select Alt on a Windows computer, a letter or number appears under the tabs on the ribbon. These include the:

File
Home
Insert
Page Layout
Formulas
Data
Review
View
Help
Save Icon

When you press the letter or number under the tab, letters or numbers will appear under the commands for that task. You can then use shortcuts to do whatever you need.

For example, when you press Alt and W under the View tab, a Q appears under Zoom. Then, selecting Q allows you to enlarge the information on the worksheet.

Editing Operations

After you use some of the shortcuts, you may want to make corrections by undoing something, or you may want to copy data to paste it into another row or cell. These shortcuts are self-explanatory.

Copy: Ctrl + C (Windows and Mac)

Paste: Ctrl + V (Windows and Mac)

Undo: Ctrl + Z (Windows and Mac)

Cut: Ctrl + X (Windows and Mac)

Spellcheck: F7 (Windows and Mac)

Formatting Operations

Some formatting functions will be used more than others, so the most common ones are listed here. These shortcuts don’t need any further explanation either.

Bold: Ctrl + B (Windows) ⌘ + B (Mac)

Italic: Ctrl + I (Windows) ⌘ + I (Mac)

Underline: Ctrl + U (Windows) ⌘ + U

Data Editing Operations

If you need to fill the same information in other cells or rows, these shortcuts will help do it.

Fill down from cell above Ctrl + D (Windows and Mac)

This shortcut allows you to fill cells with the same information from the cell above it. So, if you’re tracking inventory and the costs for a group of products are the same, you could use this shortcut to fill in the information on the worksheet.

Fill right from the cell to the left: Ctrl + R (Windows and Mac)

If you need the same information from a cell to the left of the one you’re on, this shortcut allows you to copy the information to the cell.

Find and Replace: Ctrl + F (Windows and Mac)

If you need to find information on a worksheet, this shortcut allows you to find it, and replace it with new data if necessary.

Calculations Operations

Probably the most common calculation on an Excel worksheet is addition, followed by multiplication. This shortcut allows you to add or multiple cells or rows or insert functions.

Insert Autosum Formula: Alt + = (Windows) ⌘ + Shift + T (Mac)

Insert Function: Shift + F3 (Windows and Mac)

There are 200 Excel shortcuts in Windows and 200 for Mac, but these 20 are probably the most popular and frequently used shortcuts for people who use the application at work.

How to Memorize Excel Shortcuts

Image via Udemy

The way to learn anything is by doing it. While some people are visual learners, most people memorize physical tasks better by doing them and using shortcuts on a keyboard is a physical task.

Some of the shortcuts used in Excel are also applicable to other Microsoft applications. For instance, you can use the find and replace shortcut, Ctrl + F, with MS Word. A new word document can also be opened in MS Word by using the shortcut Ctrl + N. Using F7 allows you to spellcheck on Excel and MS Word.

The duplication of some of the shortcuts can make them easier to learn and apply to applications. However, if you need more guidance as you learn shortcuts for Excel, there are other resources.

Laminated Cards

Although many of the shortcuts are easy to learn, some of them may be harder because you don’t use the function or formula very often. Instead of hunting for the correct way to perform an action or looking it up on the Internet, you can purchase laminate shortcut cards and keep them on your computer.

The laminated cards include all the shortcuts for both Windows and Mac computers listed together. Since they are laminated, the cards can last for as long as you need it. The cards are available from online retailers or stores that sell office supplies.

Online Classes

If you want to learn more about Excel, including the shortcuts, several websites offer Excel tutorials or classes. The website corporatefinancialinstitutite.com offers the free Excel Crash Course that includes learning the shortcuts, functions, formulas and other tips.

The site excelexposure.com also offers free Excel lessons and has a free shortcut tip sheet available on their site. There are several other websites that also offer free Excel courses or tutorials that include learning the shortcuts you use every day.

Write Shortcuts Down

Since you may only use a few of the shortcuts, write down those you use the most to help you learn them. Writing things down helps many people remember information better than just reading it. A study published in Psychology Today says that writing down information boosts memory and the ability to retain concepts.

Write the shortcuts you use most horizontally on a piece of paper, cut it out and then tape it to the top of your monitor. It can be used as a reference in case you need it, but you will probably retain many of them after you’ve written them and used them at work every day.

Learning the shortcuts that you don’t use often can be more difficult, but if you print out a tip sheet or buy the laminated sheets, you can keep them nearby for reference when and if they are needed. Some of them you may not ever use, but it is better to have the information nearby in case you need it.

By learning Excel shortcuts, such as the 20 listed above, you will be able to produce spreadsheets faster and more accurately than you did before. Using a mouse and the ribbon to complete actions on a worksheet takes more time, and if you are unsure of how to perform an action on Excel, it can be inaccurate.

However, by taking the time to learn the shortcuts you use the most, you could be confident that you’re performing the right and your work will be more precise.

Learn How to Unhide Cells in Excel Using Keyboard Shortcuts or the Home Menu

How to Hide and Unhide Data in an Individual Cell

While Excel does not allow you to Hide and Unhide individual cells using the Hide/Unhide command, here’s a trick showing how to hide just one cell:

Choose the cell or cells you want to hide
Select Cells from the Format menu and the Format Cells dialog box will appear
Select the Number tab
From the list of format categories, select Custom
Enter three semicolons (…) in the Type box

This causes the information in the cell to disappear, and it won’t print. However, you will be able to see the cell information in the Formula Bar. To unhide that individual cell, enter any other type of information.

Hiding Data in Columns and Rows

Hiding data in columns and rows still allows you to reference the data in formulas and charts. Also, hidden formulas that contain cell references will still update if the data in the referenced cells changes.

How to Hide Data in Excel Using Shortcut Keys

First, we’ll discuss how to hide columns and then we will discuss rows.

How to Hide One or More Columns

The shortcut keys for hiding columns is: [Ctrl] + [zero]. Here are the steps:

Choose any cell in the column you want to hide, making it the active cell
Press and hold Ctrl
Press 0 [zero] while holding Ctrl
The entire column with the active cell and any data it contained, will be hidden

Note: Hide multiple columns with this shortcut by highlighting at least one cell in each column you wish to hide, then repeat steps 2 and 3 above.

How to Hide One or More Rows

The shortcut keys for hiding rows is: [Ctrl] + [9]. Here are the steps:

Choose any cell in the row you want to hide, making it the active cell
Press and hold Ctrl
Press [9] while holding Ctrl
The entire row with the active cell and any data it contained is hidden

Note: Hide multiple rows with this shortcut by highlighting at least one cell in each row you wish to hide, then repeat steps 2 and 3 above.

How to Hide Columns or Rows Using the Home Menu

This method has three options on how to unhide columns in excel, depending on the object selected when the menu is accessed.

To Hide a Single Column or Row

Click on the header of the column or row that you would like to hide and the column or row will be highlighted
On the Home tab, in the Cells group, select Format
Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
The selected column or row and any data is hidden (the header is also be hidden)

To Hide Adjacent Columns or Rows

To hide two or more side-by-side columns or rows:

In the column or row header, click and drag across all of the columns or rows you want to hide
On the Home tab, in the Cells group, select Format
Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
The selected column or row and any data is hidden (the header is also be hidden)

To Hide Non-Adjacent Columns or Rows

In the column or row header click on the first column or row you want to hide
Press and hold Ctrl while clicking once on each additional column or row you want to hide.
Release Ctrl
On the Home tab, in the Cells group, select Format
Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
The selected column or row and any data is hidden (the header is also be hidden)

How to Unhide Columns in Excel

To Unhide All Hidden Rows and Columns Simultaneously

Select all of the cells by pressing Ctrl+A or the gray Select All button in the upper left corner of the worksheet.

Note that if your worksheet has data and the active cell is above or to the right of the data, Ctrl+A selects the current region. Press Ctrl+A again to select the entire worksheet.

On the Home tab, in the Cells group, select Format
Do one of the following:
- Under Visibility, select Hide & Unhide, and then Unhide Columns (or Unhide Rows)
- Under Cell Size, click Column Width or Row Height, then type the value that you want in the Column Width or Row Height box
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box

To Unhide the First Row or Column of Your Worksheet

If you’ve hidden the first row or column, take the following steps:

Select the first row or column using one of the following:
- In the Name Box next to the formula bar, type A1
- On the Home tab, under Editing, click Find & Select > Go To. Type A1 in the Reference box, then click OK.
On the Home tab, in the Cells group, select Format
Under Visibility, select Hide & Unhide, and then Unhide Columns (or Unhide Rows)
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box

If you want to learn how to freeze cells in excel so rows and columns stay visible, click here.

How to Use the Average Function in Excel

What is an Average Function in Excel?

The average of a group of numbers describes the central value of the set.

The first thing to remembers is that using averages can help us to draw generalizations from sets of data.

There are three different ways that are commonly used to measure central tendency:

First is the Average: You can calculate the mean of a group of numbers by adding each value together and then dividing by the total count of those numbers. For example, in the group 2,3,5,5,5, the average is (2+3+5+5+5)/5=4. The average of a group of numbers is highly susceptible to outliers.
Second is the Median: The median is the number that lies directly in the middle of a set of numbers. For example, in the group 2,3,5,5,5, the median is 5.
The third is the Mode: The mode is the number that occurs most frequently in a set of numbers. In the example group of 2,3,5,5,5, the number 5 appears three times, making it the mode.

When Do Averages Come in Handy?

Averages aren’t just a part of your grade school math curriculum.

There are plenty of applications for averages in the real world, both at home and in the office. Notably, you can use averages to draw conclusions about your budget, your grades, yearly earnings, and even your car’s gas mileage too.

With this in mind, there are countless ways that averages can come in handy. However, you may want to look at different values depending on your situation.

The mean is best used with datasets that contain information that’s evenly spread, such as bell curves.

Extreme outliers in either direction can skew results and lead to false conclusions about central values in a dataset. It is important to realize that when outliers are in play, you should stick to using the median to represent the central value.

The mode works well for smaller sample sets. That is where there’s not enough data to draw relevant conclusions by calculating the mean or median.

The Basics of Excel’s Average Function

The AVERAGE function in Excel returns the mean number for any data set as opposed to the median or mode. In addition, it can be used as a worksheet function, making it easy to enter as part of a formula in a cell.

Your average will depend on the cells that you highlight. For this reason, it may be best to omit any statistically insignificant outliers. A value that’s too high or not high enough can skew your results, giving you an inaccurate picture of your dataset as a result.

Using the Average Function in Excel

Using the AVERAGE function is simple enough for even Excel novices to master in a matter of minutes. As a worksheet function, all you need to do is enter the formula correctly into a free cell to get a mean value.

The syntax you should use to find the arithmetic mean of a data set is:

AVERAGE (number1,[number2], …)

There are very few components that you’re required to input into this formula. There’s only one parameter that you really need to fill out in order to yield an accurate result.

Number1: This field is required. You can put in a cell value or a range of cells for which you want the average.
Number2: This field is optional. If you want to analyze additional cells or ranges, you can add up to 255 more to the AVERAGE formula.

If any of the cells in the range you highlighted contain text, logical values, or are simply empty, the AVERAGE formula will ignore these. Cells that contain a zero, however, are included. If you want to omit any cells within a row or column, you should leave them blank.

If the AVERAGE function isn’t coming up with the results that you’re looking for, then you may want to consider modifying the formula slightly.

The AVERAGEA and AVERAGEIF Function

The AVERAGEA function allows you to include logical values and text representations of numbers in a reference as part of your end result. This also gives you more control over formatting.

Moreover, you can also combine the AVERAGE function with the IF function to limit the range of values used to calculate a result.

Excel’s IF function allows you to see whether a data point meets a condition that you specify. For example, being greater than another number or occurring within a given timeframe.

The AVERAGEIF function gives you the power to calculate a mean using only values that meet certain criteria.

The Average Function in Action

Here are some examples of common formulas used to find the average of various sets of data:

Single column – Average the last few values in a single column: =AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Different columns – Average the last few values in different columns: =AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
A range of numbers – Average the last few values in a range of numbers:

{=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3…}),ROW(data), data))}

Average the top scores in a data set: =AVERAGE(LARGE(range,{1,2,3…}))

Conclusion

Ultimately, average values are an important part of everyday life. It helps us to quickly and easily understand even large datasets by giving us the central value of a group of numbers. Excel makes it easy to calculate mean values using the AVERAGE function.

Finally, we hope that this tutorial has helped you to better understand how to use the AVERAGE function in Excel.

Indeed the average function in Excel is such a powerful tool when it comes to analyzing data.

Do you need help in freezing columns in Excel? Here is a tutorial on how to do that.

Select Excel’s Used Range on a Mac

I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button.

The used range on a worksheet starts with cell A1 and ends with the last used cell in the worksheet. This “last cell” is not always apparent, but easily found. Just use the keyboard shortcut CONTROL + G to bring up the Go To dialog box.

Click Special… which will bring up the Go To Special dialog box.

Select Last cell and click OK.

The last cell may sometimes surprise, because Excel considers cell formatting as being “used” so you may see blank cells that are way outside your data range. Tip: Sometimes you can delete the seemingly extra rows and columns outside your data range and it will reduce the file size.

Select the Used Range by Navigating Back Home

Once you find the last cell, you can then hold the Shift key down and click cell A1 to select the entire range. Of course if you can’t see cell A1 in the current window there is no Home button on the Mac to help you out. (Major bummer)

The next best thing is to hold the COMMAND + Shift keys down while you tap the left arrow and up arrow keys until you reach cell A1. This can be simple, or time-consuming depending upon size and shape of your worksheet.

Selecting the Used Range

To select the entire used range with VBA is a simple matter. Choose Tools > Macro > Visual Basic Editor, then choose View > Immediate Window, and type activesheet.usedrange.select inside the immediate window and hit enter.

Create a Macro to Select the Used Range

You can also create a macro to select the entire used range by opening the VBA Editor, inserting a Module, and entering the following code.

Sub ActiveSheetUsedRange() ActiveSheet.UsedRange.Select End Sub

Or you could get fancy with this code.

Sub SelectUsedRange() Dim rng As Range Set rng = Range("A1").SpecialCells(xlCellTypeLastCell) Range(Cells(1, 1), rng).Select End Sub

Add a Keyboard Shortcut for the Macro

To make things simpler to run the macro, you can enter a shortcut. Just choose Tools > Macro to bring up a dialog box.

To add a shortcut, select a macro and click Options… which will bring up the Macro Options dialog box.

Enter a shortcut key by clicking inside the Shortcut key box and pressing a key on the keyboard. I pressed the “u” key on the keyboard, and consequently the keyboard combination is shown as Option + Cmd + u, as you can see in the screen shot. (Depending on the key, you may also include COMMAND, Control, Shift into your shortcut.)

Enter a description if you wish, and then click OK. Select the Cancel button on the Macro dialog box to make it disappear.

Now you can run the macro by simply using the keyboard shortcut Option + Cmd + u.

Note: This macro will not work if a Chart sheet is selected.

Used Range verses Actual Used Range

The actual used range might be different than the used range. Meaning that some blank cells that are formatted might be included in the used range. Most likely you will only want to deal with a range that has some actual values. This would be the actual range.

Please refer to the aforementioned blog post over at Contextures to see a couple of different examples of code that you can use to select the actual used range. These examples are short and use the VBA FIND function to get the job done.

Settings and Shortcuts for Excel 2003

I recently found myself with a new, loaner laptop and a brand new copy of Excel 2003 as my default spreadsheet program. This was fine until I realized there were a number of things “missing.” Like shortcuts and settings that I’ve changed over the years to make Excel serve me, instead of the other way around.

So here’s my list of things I do to “normalize” Excel 2003.

Full Menu’s

My number one pet peeve with Excel is they “automatically customize menus and toolbars based on how often you use the commands.” But my question is, “How do new users know what menu commands are available if they’re hidden?”

I like to use “full menus” so you see ALL menu commands each time each time you click a menu. This is a standardized approach; you see the same thing each time you click a menu.

Here’s how it’s done:

General Options

I have only two recommendations here: maximizing the recently used file list and minimizing the number of worksheets in a new file.

Recently used file list (9)

I like to set the recently used file list to the maximum number. In Excel 2003 that number is nine. This saves you time when searching for a recently used file.

Here’s how it’s done:

Recently used file list

Sheets in new workbook (1)

When creating a new spreadsheet file, how many sheets do you actually use? How many times have you looked over a spreadsheet file from someone else and clicked on those bank sheets to see if they contained anything?

Remember the days when a new file had 16 worksheets as the default? Excel 2003 has just three. That’s two to many. I like to set the number of worksheets in a new file to one. If I need another, they’re easily created.

Here’s how it’s done:

Sheets in new workbook

Shortcuts on the Toolbar

These custom toolbar buttons are necessary when using Excel 2003: Paste Values, Freeze Panes, Current Region, Auto Filter, and Pivot Table. You can easily customize the toolbar to add these and more.

For each addition to the toolbar you’ll need to access the Customize dialog box. The long way is to choose View > Toolbars > Customize… or the short way is to right-click a toolbar and select Customize… from the pop-up menu.

Once the Customize dialog box is open, select the Commands tab. Now your ready. Here’s my favorite custom toolbar commands.

Paste Values

The very first custom toolbar command icon I put up. Indispensable. Well, almost.

Here’s how it’s done:

In the Categories pane select Edit. In the Commands pane scroll down and find Paste Values. Click and hold the left-mouse button down while dragging the icon to a toolbar.

Freeze Panes

Another indispensable toolbar command button shortcut icon for anyone who works with lots of data in proper tables.

Here’s how it’s done:

In the Categories pane select Window and Help. Scroll down until you find Freeze Panes. Click with left mouse button, hold and drag to the toolbar.

Select Current Region

Here’s a neat button that allows you to find the shape of a data region by selecting it for you automatically. Better than the keyboard shortcut Ctrl+Shift+asterisk (*). Reminds me of the CurrentRegion property in VBA.

Here’s how it’s done:

In the Categories pane select Edit. In the Commands pane scroll down to the bottom and find Select Current Region. Click and hold with the right-mouse button while dragging to a toolbar of your choice.

AutoFilter

A great button to save you some time when filtering tables. If the active cell is on the header row it simply turns on the filter. However, if you select a cell in the data that contains something you want to filter, clicking the AutoFilter will turn on AND filter that selection for you. A one step process that saves time.

Here’s how it’s done:

Select Data from the Categories pane. Click the AutoFilter command with the left mouse button, hold and drag to a toolbar.

PivotTable

This command serves to initiate a PivotTable from a data table. It also brings up the PivotTable and PiotChart Wizard when you are working on an active PivotTable.

Here’s how it’s done:

Select Data from the Categories pane. Scroll down to the bottom of the Commands pane and find PivotTable and PivotChart Report icon. Left-click, hold and drag to a toolbar.

More Stuff

Find your favorite command not shown on a Toolbar and load it up. Pronto!

That Damn Delete Key in Excel for Mac

I have no earthy idea why it took me so long to figure out how to delete the contents of a cell or range in Excel for Mac. Ever since I bought my MacBook Pro I’ve known the Delete key on a Mac isn’t really a Delete key.

I mean, since my background is with Windows, I have ingrained knowledge on how the Delete Key works on a computer. Ingrained, I tell you.

But all of that knowledge was shattered upon getting a Mac.

Where Is the Excel Delete Button on Mac

After some consternation, I learned where is the delete button on a Mac. To press the delete button on Mac computers you have to hold down the fn key and the Delete key at the same time when you want to delete something on a Mac. (Skip to video)

After a while, you get used to the idea that the Delete key on a Mac is really a backspace key and using fn+Delete gives you the real Delete key action. 🙂

Of course if you’re a long time Mac user you probably think I’m cuckoo. But hey, this is my blog, think what you like. I’m not the only one who’s decided to start using a Mac after a lifetime of Windows abuse use.

Excel for Mac

Anyway, when using Excel on a Mac — I’ve got versions 2008 and 2011 — you run into a learning curve with all the unusual shortcut keys, function keys (1, 2), and menu and ribbon things that are different from the Windows version of Excel. So there’s a tendency to forget about how the Delete key works on a Mac.

I mean, this is Excel we’re talking about here. Hitting the Delete key is supposed to delete the contents of the active cell, for cryin’ out loud.

In Excel for Mac it does that, but the cursor also gets stuck inside the cell in edit mode. You have to hit the enter key to finish deleting the contents, but this act also moves the active cell to the next cell down.

And if you’ve selected a range and hit the Delete key, the active cell contents are deleted and the cursor is stuck inside the cell in edit mode. You have to hit the Enter key, which does nothing but take you to the next cell. The range contents are still there, with the exception of the active cell.

Not the kind of behavior that occurs in Excel for Windows.

How to Delete Cell and Range Contents in Excel for Mac

The trick is to remember that fn+Delete is really a keyboard shortcut to the Delete key on a Mac. Then the world rights itself and the planets align. Frustration abates. You’ve finally found the magic. Your mojo is back!

Watch this 54 second video to see what I’ve been babbling about for the past 454 words.

YouTube link

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.

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.

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.

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.

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

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

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

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.

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.

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

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.

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

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

I save this file to the newer XLSX format.

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.

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.

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.

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

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.

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.

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.

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.

Related Posts Plugin for WordPress, Blogger...