New ways to go! E-Book

Ina Koys

• Herausgeber: Computertrainerin.de
• Kategorie: Fachliteratur
• Sprache: Englisch
• Veröffentlichungsjahr: 2023

Beschreibung

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 and much more. All explained using clear examples, suitable for normal users and insiders.

Leseprobe

New ways to go!

Modern Excel featuresmaking your work easier

Ina Koys

Short & Spicy, vol. 18

Contents

What we’re going to look at

Skills to bring

Ribbon and Quick Access Toolbar

Page Layout

Track changes

Flash fill

Format as Table

Quick Analysis

Sparklines

Conditional Formatting

Sheet View

Matrix calculations

New entries in the Formulas library

FILTER(), SORT() and SORTBY()

IFS()

SWITCH()

UNIQUE()

XLOOKUP()

LAMBDA()

3D-Maps

Data Types

Custom Pivot layouts

More

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 colours. Now we can see Excel tries to multiply 300 Dollars by the word ‘gross’. That, of course, will fail. But what made Excel attempt to do so? Because, by default, the cell recognizes the cells to refer to by their position relative to it’s own position. In the case of cell C15 with the correct $ 109, it will be “I’m asked to multiply the cell on my left by the cell 3 rows above me and then, add again the one left of me”. There, this policy is sensible and executable. For the other cells, it’s partly nonsense, partly impossible. Now what we need is a way to make Excel understand it’s C12 that matters and is not to be altered at all. This is, what the $ sign can do for us. We put it before any part of the reference that is to be kept:

C12 – relative reference

Dragging the initial cell, the reference to the new target cell slides according relative to the new cell position.

---ENDE DER LESEPROBE---