Loading...

Follow Excel University Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid
Excel University Blog by Jeff Lenning - 2w ago
For decades, Excel users have had the CHOOSE function and it has been able to power some wonderful formulas. But recently, we received the SWITCH function which provides much more flexibility. From a high level, both functions enable us to analyze a value and then return various results based on that value. But, SWITCH offers […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this post, we’ll learn how to add up a column of numbers based on the values in another column. For example, we are trying to analyze product sales based on average customer rating. That is, customers rate our products on a scale of 1 to 10, and so each product has an average rating […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Excel University Blog by Jeff Lenning - 1M ago

In this post, we are going to use the data model to improve our reporting process. This is the fourth post in the Stop Wasting Time series, and this is where everything we’ve learned comes together. By the time we have finished this post, updating our report will be extremely easy. In fact, it will […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Excel University Blog by Jeff Lenning - 1M ago

We are in the middle of a series called Stop Wasting Time, where we are learning about Excel features that help us improve how we update a report. In the beginning of our journey, we were updating the report manually. Now, as we’ve learned more, we’ve been able to automate parts of it. That is, […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I think it’s about time for another breakout puzzle … yes! This breakout puzzle has an astronomy theme … you know, stars, constellations, the milky way, telescopes, and so on. I’ve always enjoyed looking up at the night sky. Being able to recognize stars and constellations helps me feel at home when I travel to […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Excel University Blog by Jeff Lenning - 2M ago

This is the second post in the Stop Wasting Time series. In this post, we learn how Power Query can help us update our report in less time. After all, why would we want to spend more time than necessary updating it? That is called “wasting time” and we just don’t like wasting such a […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Excel University Blog by Jeff Lenning - 2M ago

This is the first post in a series that shows how to transform a monthly report from being updated manually to automatically. Along the way, we’ll see how classic Excel features like tables and SUMIFS can help somewhat, and then how modern Excel features like Power Query and the data model can help even more. […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The past several posts have illustrated various ways to apply dynamic array formulas and their spill ranges. Kent asked if we could use spill ranges to create a dynamic chart to visualize the future value of an investment portfolio. For example, start with someone’s current portfolio value and then apply an assumed growth rate and […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Let’s say we have a data-entry table, where we type or paste new transactions. We would like a copy of each transaction to be directed to a specific worksheet, depending on the transaction type. For example, sales transactions should flow from the data-entry table into another worksheet that displays all sales transactions. Returns should flow […]
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
One reason we love PivotTables is that they automatically expand to include new items. For example, we have a PivotTable that summarizes transactions by account. Then next month, we paste new transactions into the data table and there is a new account. To get this new account included in our report, all we do is […]
Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview