Fixing Bloated Excel Files

Often times, excel files grow to a size much larger than is necessary considering the quantity of data stored in them. Sometimes this is due to an excess of pictures, formatting, or drawing objects within the workbook, or hidden sheets that contain a lot of data, but 9 times out of 10, the problem is with dirty cells. By dirty cells, I am referring to cells that once contained data, but have since been cleared. Excel needs them to be cleared in a specific way before it will free up the associated memory.If your files seem bloated, a very common cause is Excel thinking that some data is present in some place quite distant from the actual end of data. If your scrollbar goes way past your data lots of scrollbar travel left, even though you are at the bottom of the data, then you need to clear the blank rows and columns beyond the range of your data.Go to the bottom row of data, then select the entire next row by clicking on the row number. Then press the keys “CTRL” + “Shift” + “Down Arrow” to select all the remaining rows in the worksheet. Right click within your selection, and choose “Delete”. Now when you save the workbook, the scroll bar should instantly expand, showing that there is less scrolling range. You can do the same if your horizontal scroll bar is going too far. Select the first blank column, “CTRL” + “Shift”+ “Right Arrow” will take you to the last column, and then delete the selection. If you can identify several sheets with excess scrolling space such as this, and delete the unused rows and columns, you should find the file size greatly reduced.Warning: You cannot simply select these cells and hit the “Delete” key on your keyboard. This key actually tells Excel to “ClearContents” of the selected cells. The cells may still remain dirty because of individual formatting settings, especially if the original content was copy-pasted in.

via Beginners Guide to Improving the Speed and reducing the Size of Excel files. – Excel, Filesize, size, speed, efficiency, slow, faster, bloat, smaller, shrink.

Show Day Names With Your Dates (XL2000-XL2007)

If you ever need to show the name of the day in addition to the dates (e.g. Saturday, July 10) here are a couple of options.

Option 1: You can create a custom number format for the cells containing the dates

1) Press CTRL+1;

2) Select the Custom category;

3) In the Type field enter dddd, mmm y

Option 2: You can use the TEXT function to return a custom date based on a date in another cell.

Type =TEXT(A1,”dddd, mmm y”) where A1 is a cell containing a date.

With both of these options, you have lots of flexibility for creating date formats by using any combination of the following codes.

d – will return the day as a number without a leading zero (e.g. 1)

dd – will return the day as a number with a leading zero (e.g. 01)

ddd – will return the abbreviated weekday (e.g. Sun)

dddd – will return the full weekday (e.g. Sunday)

m – will return the month as a number without a leading zero (e.g. 1)

mm – will return the month as a number with a leading zero (e.g. 01)

mmm – will return the abbreviated month (e.g. Jan)

mmmm – will return the full month (e.g. January)

yy – will return the year as a 2-digit number (e.g. 10)

yyyy – will return the year as a 4-digit number (e.g. 2010)

You can also include commas, dashes, slashes, parentheses, within the format code. For example, the code (dddd) mmmm d, yyyy will return (Saturday) July 10, 2010.

via Microsoft Excel Tips.

The Excel Camera tool enables you to take a live picture of a range of cells that updates dynamically while the data in that range updates. Although Microsoft doesnt include the Excel Camera tool in the mainstream Ribbon, its quite useful when you want to build Excel dashboards and reports. Before you can use the Camera tool, you need to add it to your Quick Access Toolbar QAT.The Quick Access Toolbar is a customizable toolbar in which you can store frequently used commands so that theyre always accessible with just one click. You can add commands to the QAT by dragging them directly from the Ribbon or by going through the Customize menu.Follow these steps to add the Camera tool to the QAT:1. Click the Office icon in the upper-left corner of Excel.2. Select the Excel Options button to activate the Excel Options dialog box.3. Click the Customize button.4. In the Choose Commands From drop-down list, select Commands Not in the Ribbon.5. Scroll down the alphabetical list of commands and find Camera; double-click to add it to QAT.6. Click OK to close the Excel Options dialog box.When youve taken these steps, youll see the Camera tool icon in your Quick Access Toolbar. Not surprisingly, the icon for the Camera tool looks like a camera.

via Adding the Excel Camera Tool to the Quick Access Toolbar – For Dummies .