Get Data from Website that Requires a Login

I’m not sure how this will work out when you try to get your bank account information as most of them will require 2FA (two-factor authentication).  But for other sites that require a login, this is just real cool!

Daily Dose of Excel

via Get Data from Website that Requires a Login.


Understanding Column Widths – A Bit of History

This is some cool trivia information on how column with is determined.

Ever wondered what does 8.43 in the default column width mean?  read the link to find out.

ExcelTips Daily Nuggets

via Understanding Column Widths.

Setting the Default Font Size for Comment Balloons

Another gold nugget found.

This has always been a pain as the font size may be a little small.

These simple steps will solve it. 🙂

  1. Right-click anywhere on the desktop. Windows displays a Context menu.
  2. Choose Properties. Windows displays the Display Properties dialog box.
  3. Make sure the Appearance tab is selected. (Click here to see a related figure.)
  4. In some versions of Windows you should click the Advanced button. Windows displays the Advanced Appearance dialog box. (Click here to see a related figure.)
  5. Using the Item drop-down list, choose ToolTip.
  6. Using the other controls in the dialog box, change the font specifications as desired.
  7. Click OK, as necessary, to close all the open dialog boxes.

from: ExcelTips Daily Nuggets

via Setting the Default Font Size for Comment Balloons.

Excel 2010 Games: Missile Command and Tower Defense

Free games coded in Microsoft Excel.  Now isn’t this amazing 🙂

I’m gonna download them to try

via Excel 2010 Games: Missile Command and Tower Defense.

Absolute and Relative Referencing

Referring to another location

Microsoft Excel uses the “A1” format in cell

The alphabet (“A”) refers to the column
position, while the number (“1”) refers to the row position.

If you key in the formula “=A2”, it will refer
to and display the cell value in the Cell A2 (Column A, row 2). And when you copy the cell to other
places, the reference will move according to the row and column (see picture above). This is relative referencing (the
reference is relative to the position of your cell). However, there are times when we want to
fix the reference, have the cell point to the same location even if we copy and
paste to other places.

Absolute Referencing

Microsoft Excel uses the Dollar sign (“$”) to
fix the reference of a cell. Using
the same example above, by keying in “=$A$2”, we will still refer
to and display the cell value in the Cell A2.

But when we copy it to other location, the reference no longer moves, and is
now Absolute.

Combining both Absolute and Relative Referencing

Microsoft Excel allows you to fix either the column or row
reference by placing the “$” in front of either.

In the example above, I’ve fixed the row reference, so
when I copied the cell across the area, the reference cell will follow the
column, but will remain pointing to row 2.

In this next example, I’ve fixed the column reference,
so when copied across, the cell reference follows the row, but remains pointing
to column A.

Keyboard Shortcut

You can use the “F4” key to cycle through the
various absolute referencing options when you key in formula. Just make sure that your cursor is
pointing at the correct cell in the formula.

Practical Examples

In this example, we have 3 products being purchased by 3
customers and we want to know how much each customer has to pay.

Because the price is in column B, when calculating the
amount for each customer, we have to fix the reference to the price in column
B. As above, in my formula, we fix
the cell reference as $B3, so that when we copy across to the rest of the
cells, it will follow the customers and product, but remain pointing at the
correct price column.

Add a Table of Contents to your Workbook Programmatically

If you need to create a Table of Contents for your workbook and update it periodically, this is a God-send!

I’ve just come across this link and not tried it out.  But based on just reading the codes, this will either update an existing TOC or create one if you don’t have it yet.

There is a manual portion though, you need to be at your computer to manually close the print preview screens generated.  The good side is that you decide how many pages each section/tab is, the downside, you can’t just run it and go off for a cup of coffee.

via Add a Table of Contents to your Workbook Programmatically.

Too many different cell formats

Many times we will encounter the following error messages in our excel files:

Too many different cell formats
No more new fonts may be applied in this workbook

Since this is a common question that I get, let me document down the causes and prevention method.

To help users manage file size, Microsoft Excel allows only a total of 4,000 different cell formats and 512 different fonts.

Note that for Excel, different cell formats include different Font Size, Font Colour, Cell Shading, Bold, Italics, Underline, etc.

The following are the 2 most common causes of your workbook exceeding the allowed number of cell formats.

    1. Copying many different worksheet from different other workbooks that contain different formatting
    2. Allowing “auto scale” in Graphs and Charts
      This will cause problem when you have many charts, which you have resized, resulting in different font sizes in each chart’s title, axis and value formats
      If you add charts manually or if you copy and paste existing charts, you can reach the font limitation for a workbook.

For the above problems, the solutions are as follows:

    1. Apply standard fonts to all your worksheets
      Using the same font for all cells reduces the number of formatting combinations
    2. Disable “auto scale” in your Graphs and Charts
      This will fix the font sizes in your charts to prevent accidental creation of additional fonts.
      As I understand that most of you will have already created many different charts in your workbook, a macro script is available here for you to automatically disable the “auto scale” function in all existing charts that are in open workbooks.

You can take the following proactive steps to prevent this problem from recurring:

    1. Using “Format > Styles” to format your cells
      This will ensure consistent application of standard formats across your workbook.
      Many of you should be already familiar with using the “Comma” and “Percent” styles
    2. Disable “auto scale” whenever you insert or manually create a Graph or Chart.
      This will prevent problems in the future when you copy the chart

I hope this note will shed light on this problem that many of you face and allow you to take action to prevent it from recurring.