|
|
  Do your spreadsheets smell? This week, a tweet from Felienne Hermans caught my eye.
- "Our @icse2012 paper on spreadsheet smells already has a citation before publication"
Spreadsheet smells? I've seen some stinky spreadsheets, but have never read a conference paper on spreadsheet smells. It sounded intriguing, so I followed the link to Felienne's paper – Detecting and Visualizing Inter-worksheet Smells in Spreadsheets.
Code Smells
The starting point for the paper is the code smell metaphor introduced in Martin Fowler's book, Refactoring: Improving the Design of Existing Code. I don't have that book, so I visited Wikipedia, to see what it knew about code smells.
Fortunately, Wikipedia had a helpful summary of common code smells, and I've listed a few of them below. Can you see how these code smells relate to Excel, whether you're building worksheets, or creating Excel VBA code?
- Duplicated code: identical or very similar code exists in more than one location.
- Long method: a method, function, or procedure that has grown too large.
- Contrived complexity: forced usage of overly complicated design patterns where simpler design would suffice.
- Excessive use of literals: these should be coded as named constants, to improve readability and to avoid programming errors.
Hmmm…replace "code" with formulas, and you've probably seen (or created) workbooks that had those code smells. I've been guilty of creating some of those smells, and have seen workbooks start small, and slowly grow out of control.
Spreadsheet Smells
Among the most frequent spreadsheet smells that Felienne and her colleagues found were:
- Inappropriate Intimacy – a worksheet that is overly related to a second worksheet.
- Feature Envy – if there is a formula that is more interested in cells from another worksheet, it would be better to move the formula to that worksheet
- Shotgun Surgery – a formula F that is referred to by many different formulas in different worksheets…chances are high that many of the formulas that refer to F will have to be changed if F is changed.
Read More About It
If you'd like to learn more about spreadsheet code smells, take a look at the paper written by Felienne and her colleagues, to see how their research was done, and what their conclusions were.
I'll be reading it again this weekend, to see what else I can absorb from it. Have you read anything similar, or heard about code smells before?
_______________
To format a pivot table, you can select a specific section, such as one of the fields, or a grand total. When you point to a field heading, a black arrow will appear, if the Enable Selection setting is turned on.
In the screen shot below, you can see the black arrow at the top of the Product field. Click in that spot, and all the Product item labels are selected.
Click in that spot again, and the Product heading is selected, instead of the item labels.
Pivot Table Field Setting Quick Tip
Instead of a single click on a heading cell, you can point to an outer field heading and double-click when the black arrow appears. In the screen shot below, the black arrow is on the Bran product heading cell.
Note: This trick won't work on an inner field, like Region, which has no other fields under it.
Double-click on the outer field heading, and the Field Settings dialog box opens. In there, you can change the layout and other settings, and add or remove subtotals.
Another way to open the Field Settings dialog box is to right-click on an item, and click Field Settings in the popup menu. This works for both inner and outer fields in the pivot table.
I find the double-click shortcut to be quicker and easier – as long as you remember to point somewhere that the black arrow appears.
Watch the Pivot Table Selection Video
To see the steps for selecting section of an Excel Pivot Table, you can watch this short video tutorial.
_________________
You can add a chart in Excel, based on worksheet data, like this sales chart example.
But, if you filter the data, and rows are hidden, that data also disappears from the chart. You might like that feature, and not mind that the chart changes. In the example shown below, only the West region's data is visible on the sheet and in the chart.
However, there are some situations when you want the chart to show the data, even if the data is hidden. In the next example, columns A:C are hidden, so that they don't distract from the chart. Oops! Now there's nothing in the chart. That's not the effect that you were going for.
Change a Chart Setting to Show Hidden Data
If you want your chart to show all the data, even if some of the source data is hidden, you can change one of the chart settings. The Excel 2010 instructions are shown below, and you can click here for instructions to show hidden data in Excel 2003 charts.
To change the setting in Excel 2010:
- Click on the chart to select it, and on the Excel Ribbon, under Chart Tools, click the Design tab
- Click the Select Data command
- Click the Hidden and Empty Cells button
- Add a check mark to 'Show data in hidden rows and columns'
- Click OK, twice, to close the dialog boxes
Now, you can hide rows or columns, and the chart data will remain visible.
Watch the Show Hidden Data in Chart Video
To see the steps for changing the chart settings in Excel 2010, you can watch this short video tutorial.
__________________
If you want to enter data in an Excel worksheet, while keeping the data sheet hidden, you can create an Excel UserForm.
I've updated my sample file, so you can now add new parts, while entering data. It's almost working the way it should, but I'm stuck on one step, so if you have a solution, please let me know!
[Update: Problem solved with a workaround -- see below.]
Select a Part from the ComboBox Drop Down List
In the sample file, you can click the Add Parts Information button, to open the UserForm.
Then, select a Part ID from the combo box drop down list.
The Parts List
On another sheet in the workbook, there are two lists – Location, and Parts. These are dynamic named ranges, and will expand automatically, as new items are added to the lists.
Add a New Part to the List
In
Continue reading Add New ComboBox Items in Excel UserForm
Instead of selecting a product code in an Excel drop down list, it's usually easier to select a product name instead. However, your pricing calculations might use the product code, instead of the product name, so you need that information.
With a bit of programming in Excel, you can show a description in a combo box, but enter the matching code for the selected item into the cell. You've seen other versions of my data validation combo box code:
Double-click on data validation cell to open combo box Click on data validation cell to open combo box Use named lists with data validation combo box
Keep reading, to see how the new combo box codes example works.
Select From a Combo Box
I've uploaded a new sample file on the Contextures website, which lets you select a month name, or weekday name, from an Excel combo box.
When you
Continue reading Excel Data Validation Combo box Codes
Do you ever wish that the letters in the Excel column headers were bigger? Did someone send you an Excel file, with a different font in the headers, and you can't figure out how they did it?
Here's how you can change the settings in a workbook, so the column and row headers look different.
[Note: If you're trying to fix column headers that appear as numbers, click here: Column Headings Show Numbers]
The Default Settings
When you create a new file in Excel, the row and column headers are displayed in your workbook Normal Style's font. The Normal Style's font is also used in the worksheet cells, unless you select a different format in some or all of the cells.
In my workbooks, the default font is Arial Narrow 11. In the screen shot below, you can see that the column and row headers are in that font, and
Continue reading Change Font in Excel Column Headers
Why do good Excel macros suddenly go bad? Does that ever happen to you? Here's my latest adventure, and maybe it will help you prevent (or solve) a similar problem.
Unprotect Sheets With a Macro
When making changes to client files, I use macros to quickly and easily protect or unprotect all the sheets in an Excel file, like the examples shown below.
This macro protects all the worksheets in the active workbook, with no password. Sub ProtectAllSheetsNoPwd() Dim ws As Worksheet On Error Resume Next For Each ws In ActiveWorkbook.Worksheets ws.Protect DrawingObjects:=True, _ Contents:=True, Password:="" Next ws End Sub And this macro unprotects all the worksheets, with no password. Sub UnProtectAllSheetsNoPwd() Dim ws As Worksheet On Error Resume Next For Each ws In ActiveWorkbook.Worksheets ws.Unprotect Next ws End Sub The Protect Macro Fails
However, even a simple macro can run into problems, as I'm sure you've already discovered with
Continue reading When Good Excel Macros Go Bad
There's a story making the rounds, about a guy who rated all his online dating prospects, by using an Excel worksheet. While I'm sure we could all learn a thing or two from that guy's file, this article is about a different kind of dates – calendar dates.
Calendar dates might not be as exciting, but unlike romantic dates, you can use data validation to help keep them under control in Excel.
Entering Period End Dates
Last week in the Daily Dose of Excel blog, Dick Kusleika was looking for a more efficient way to enter a Period Ending date in his time sheet. Every two weeks he sets up a new workbook, and has to add two weeks to the previous end date.
Sometimes it's easy to do that kind of math in your head, but if you're rolling into a new month, the calculation can be trickier.
Continue reading Get Good Dates With Excel Data Validation
If you want to change the data source for a single Excel Pivot Table, you can use a command on the Ribbon.
Select a cell in the pivot table that you want to change On the Ribbon, under PivotTable Tools, click the Options tab Click the upper part of the Change Data Source command
When the Change PivotTable Data Source dialog box opens, press the F3 key on the keyboard, to open the Paste Name window. Click on the named range that you want to use, and click OK
Click OK to close the Change PivotTable Data Source dialog box. Change All the Pivot Tables in the Workbook
If you have several pivot tables in a workbook, and want to change all of them to a new data source, you can use a macro, instead of making the changes manually. I've added a new page on the
Continue reading Change Data Source for All Pivot Tables
When you're opening files in Excel, you can see the file Details, or the icons, or select another way to look at the list, such as Preview.
That Preview option sounds promising, but instead of a picture of the file's contents, you usually see this message instead – Preview Not Available. And that's not much help.
If you want to see a preview picture for an Excel file, you have to change one of the file's Property settings – keep reading to learn how. Or watch the video at the end of this article.
Change the Excel File Settings
Every recent version of Excel has a different way of getting to its Property settings, but here are the basic steps for turning on the Preview Picture option:
Open the Property dialog box Go to the Summary tab Add a check mark to the Save Preview Picture option Click
Continue reading How to Show Excel Preview Picture When Opening Files
|
|
Recent Comments