fbpx
Search
Close this search box.

Excel Tips Submitted by You [Part 2]

Share

Facebook
Twitter
LinkedIn

It is Your Week @ PHD. You get to see all the excel tips shared by PHD readers like you. I am very excited about this. Today I rushed home from work and opened the google docs to see the tips submitted by you, and found several new and fun things. You can be part of this excitement too: just click here to share your tips with us.

Here is the part 1 of the series.

The first and foremost chart construction tip by Jon Peltier

Before you take any steps towards constructing a chart, get the data right. This will save time and effort, reduce frustration, and amaze your boss, clients, and that cute young thing in accounting. Well, your boss and clients anyway.

Consider it as a set of data extracted from a database: each row is a record, each column is a field, the first row is the field names.

First row of the data range: series names as column headers.
First column of the data range: category labels or X values.
Intersection of first row and first column: overcome your natural tendency for order and uniformity, and leave this cell blank.
Data is Series in columns.

A Flurry of Keyboard Shortcuts and Productivity Hacks by Barbara

I use several shortcuts

  • F12 for ‘Save As’
  • F2 for ‘Edit’
  • F4 to switch between absolute and relative formula references, whilst in edit mode.
  • Control & 1 (using the number keys not the number pad) for ‘format cells’

If you want to copy part of a spreadsheet to a powerpoint:
Highlight the area, hold shift, click edit in the menu, copy picture, choose ‘as shown on screen’ or ‘as shown when printed’.  Paste into the powerpoint slide.

If you put the insert rows, insert columns & group icons in you toolbar you can use them for the opposite function as well. Press & hold the shift key, then click on one of the tools, it will do the opposite.  This not only saves space in your toolbar, but I find it useful as I can not easily tell the difference between the insert & delete or group & ungroup icons.

Several ways to search text using formulas by Vishy

Download these examples and play with them.
Lookup into Substring

Let’s say you want to find Age in Column D by referring to Part of First Name in cell G3 as below
First Names are in B3:B7
Ages are in D3:D7
Part of First Name Reference in G3
{ =INDEX($D$3:$D$7,MATCH(TRUE,FIND(G3,$B$3:$B$7)>0,0)) } Must be entered as an array (Ctrl + Shift + Enter)

Use SEARCH instead of FIND for case-insensitive lookup
You can also use regular expressions – Download the examples
* refers to any number of characters, including null (i.e. no character)
? refers to exactly one character, not null

Multiple Criteria Lookup
Let’s say you want to find Age in Column D by referring to First Name in cell G4 and Last Name in H4 as below
First Names are in B3:B7
Last Names are in C3:C7
Ages are in D3:D7
First Name Reference in G4
Last Name Reference in H4
{ =INDEX($D$3:$D$7,MATCH(1,($B$3:$B$7=G4)*($C$3:$C$7=H4),0)) } Must be entered as an array (Ctrl + Shift + Enter)

Some Resources to help you understand these tips:

Introduction to Excel Array Formulas

INDEX Excel Formula – Learn by Example (Used to construct a dynamic chart)

MATCH Excel Formula – Tutorial & Examples

Absolute and Relative Formula References – A Beginner Guide

Jon, Barbara and Vishy, You are truly amazing human beings. We at PHD love you for these wonderful tips.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

4 Responses to “Excel Tips Submitted by You [Part 2]”

  1. sam says:

    Vishy
    =INDEX($D$3:$D$7,MATCH(1,SUMPRODUCT(($B$3:$B$7=G4)*($C$3:$C$7=H4),1),0) need not be array entered

  2. sam says:

    opps messed it up

    =INDEX(D3:D7,SUMPRODUCT(MATCH(1,(B3:B7=G4)*(C3:C7=H4),0)*1))

    Need not be array entered

  3. [...] read: part 1 of excel tips shared by readers | part 2 | part [...]

  4. [...] year in May. We got 13 beautiful tips from our readers. You can browse through them here:  Part 1, Part 2, Part 3, Part 4 [...]

Leave a Reply