Excel Tips

Dynamic Arrays: Formulas Can Now Spill »

August 10, 2022

Let's start with the basic array formula. Go to cell E3. Type =A2:C10, as shown here. In the past, you would have had to wrap that formula in an aggregation function and maybe use Ctrl+Shift+Enter.

Get Stock Quotes »

August 1, 2022

Data types support stock quotes from 24 different exchanges around the world. Many exchanges provide data that is delayed by 15 minutes. Some exchanges only provide close of day prices.

Getting Exchange Rates Using Stock Data Types »

July 29, 2022

After Geography and Stock data types debuted, I created a YouTube video and asked people for what other data types they would like. The most popular choice was Exchange Rates, followed by Weather.

Geography, Stock, and Exchange Rate Data Types »

July 28, 2022

Office 365 subscribers will see a new Data Types gallery appear on the Data tab of the ribbon starting in late 2018 or early 2019. Initially, the feature is supporting geography and stock data types, but there are hopes that they will expand to other data types, including the ability for a company to define their own data types.

=SUM(B1:B5) is Better Than =B1+B2+B3+B4+B5 »

July 27, 2022

You have an intern working in the Accounting department who likes to add up short columns of numbers using =B1+B2+B3+B4+B5. You try to convince him that the correct formula is =SUM(B1:B5). He looks at you like you are insane, pointing out that his formula returns the exact same answer.

Add New Functions to Excel with Fast Excel SpeedTools Extras »

July 26, 2022

There are several add-ins that add new calculation functions to Excel. One of the best is from Charles Williams and FastExcel. The SpeedTools Extras package includes functions that accountants will use in everyday work.