Features Microsoft Excel Tips & How To's 2024 • Page 4 of 5 • Excel Semi Pro

Table Design in Excel

December 14, 2013March 31, 2011

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 …

Migrating to Tables in Excel

December 14, 2013March 30, 2011

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 …

Fat Fingers on the Space Bar – A Conditional Formatting Example

December 14, 2013March 25, 2011

I had the distinct pleasure of helping a colleague this week, because he fat-fingered the space bar whilst entering data in a spreadsheet then couldn’t figure out why there were #VALUE! errors on the Pivot Report. I used some conditional formatting to solve the problem and save the day. But to be fair, data entry …

Use Formula Auditing to Help Explain Formulas Excel

December 14, 2013March 21, 2011

When you need to present a complicated spreadsheet, the Formula Auditing feature in Excel visually helps to explain formula relationships. You can easily trace formula precedents or dependents. Think of precedents as “what comes before” or the inputs to a formula. If you need to show a formula precedent select a cell with a formula, …

How to Copy Data to Another Worksheet with Advanced Filter

March 2, 2024March 9, 2011

Dear Microsoft, please fix the Advanced Filter in Excel. Copying filtered data to another worksheet should be simple but it’s not. Fixing this would help out many of your beginning and intermediate users and some of us old dogs who seemingly haven’t learned the trick.

Sorting with Custom Lists in Excel

December 14, 2013March 3, 2011

Every once in a while I come across a sorting problem where a column of data needs to be sorted in a manner not consistent with ascending or descending order. Thankfully, Excel allows the sort order to come from a Custom List. As an example I have some data with Date, Name, and Type as …

Open File As Read Only in Excel

December 14, 2013February 25, 2011

To discourage users from making any changes to a macro file you can have the file recommend Read-Only when it’s opened. 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 …

Sorting Columns From Left To Right in Excel Causes Problems

December 14, 2013February 18, 2011

You may never have the opportunity to Sort columns of data from Left to Right, but beware of some apparent problems. A reader mentioned that when sorting from left to right the column width’s don’t change. I also found that formulas can be affected and for me that’s more problematic. I was asked if Excel …

Sorting Columns Left to Right in Excel

December 14, 2013February 16, 2011

Once upon a time I had a master worksheet with data in predefined columns that was used for uploading data into a legacy system. Users would submit data in worksheets they developed, but oftentimes their columns didn’t always match that of the master worksheet. So I used a little known option in the Sort feature …