New ways to go!: Modern Excel features making your work easier

By Ina Koys

You're working with Excel since long, but would like some fresh inspiration? You had an Excel training 15 years ago and wonder what all these new buttons could do for you? Then, this book is for you!

We'll be looking at all new topics from Excel 2007 onwards: Matrix operations, Flash Fill, Conditional Formatting, Sparklines, new formulas a

• Language: English
• Publisher: Computertrainerin.de
• Release date: May 4, 2023
• ISBN: 9783910233164

Ina Koys

Ina Koys is an experienced instructor for Microsoft Office. Many questions are frequently asked in trainings, but seldom covered in books. Now she answers some of them in her originally German "short & spicy" series. A little accent will add to the fun :-)

Book preview

What we’re going to look at

pretty much every office person works with Excel, often for a long time already. And mostly, we’re all using the tools that were around since ages, too. They worked 10 or 15 years ago and still do. That’s alright so far.

Still, for several tasks there are new solutions available, making our work much nicer and easier. That might be table formatting, new formulas, or Flash Fill – often they will break the mould and open totally new ways of getting work done. We’ll look at them one by one and make clear from which version of Excel onwards they are implemented.

All screenshots were done using Excel 365. Across the different versions the optics may slightly differ. Still, the features should be recognizable.

The sample files of this booklet can be downloaded from

www.ShortAndSpicy.online

Now have fun finding new insights!

Skills to bring

This booklet is for people already working with Excel. It is not terribly important weather they only do it sometimes using selected features, or maybe are very advanced. Everyone needs to bring the skills required for the job and preferably also the desirable ones. Still, we need to make sure one basic skill is provided. First, as it always was important, second, because it’s required understanding some of the following chapters: cell reference types.

To explain them, we have a little example using SUM(), the arguably most frequently used formula in Excel. After placing the cell cursor under a range of cells, we only click the Σ sign in the upper right corner of the Home ribbon. Then the SUM() formula is placed and suggests using the numerals above it.

If you agree, press ENTER to confirm, and get the sum of the numbers in the selected area. If you then want to perform the same calculation for all the columns, you don’t need to begin anew for them. You can simply hold your left mouse button down and drag the bottom right cell corner across the desired range. Excel will understand that you intend to sum up the respective figures in the columns. It delivers the correct sums. That is widely known and works reliably also for rows and of course, equations, too.

Still, this easy process will fail if constants become part of the calculation. In the next example, we’re looking for the gross result of a given net price.

After putting the equation, pressing ENTER will deliver the correct result for the current row. Still, dragging the bottom right corner down, like we did in the previous example, we get a somewhat weird result.

It can’t be correct like this. But what happened? To find out, double-click i.e., in cell C17, that, apparently, doesn’t intend to calculate at all.

Like before, the cells associated with the calculation are marked in different