Scribd
Upload
157 views6 pages

Excel Student

The document provides tips for using several useful Excel features: 1. The Recommended Charts feature suggests optimal chart types for a user's data to visualize it. 2. Pivot tables allow users to summarize, analyze, and explore large datasets without using formulas. 3. The Transpose feature switches rows and columns with a single click, saving time over manual transposition.

Uploaded by

api-385528327
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
157 views6 pages

Excel Student

The document provides tips for using several useful Excel features: 1. The Recommended Charts feature suggests optimal chart types for a user's data to visualize it. 2. Pivot tables allow users to summarize, analyze, and explore large datasets without using formulas. 3. The Transpose feature switches rows and columns with a single click, saving time over manual transposition.

Uploaded by

api-385528327
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

1.

Recommended Charts
The newly added Recommended Charts feature let’s you quickly
create a chart that’s just right for your data. This feature saves time so
you don’t have to test out every chart before picking the best one.
➤ Want the full Office suite? Get the new Office 2016 for free*
Once you’ve entered your data into Excel with column headers, go
to Insert > Recommended Charts. A window will pop-up that will show
you the best options for your data. If you don’t see a chart that you
like, click on the All Charts tab at the top to view all available chart
types.

2. Pivot tables
Pivot tables are arguably one of the most powerful and underrated
Excel tools. Especially useful for big data sets, pivot tables are a great
way to summarize, analyze, and explore your data. The best part is
that none of this requires writing a single formula.
Go to Insert > Pivot Table and select your data range. A window slides
open on the right where you can select your fields in the top half of
the sidebar and generate the table in the bottom half.

Pro tip: Similar to Recommended Charts, there’s also a Recommend


PivotTable option. With this feature, Excel suggests layouts based on
the data provided. If you’re just starting with pivot tables, this makes a
great starting point.
3. Transpose
When you want to swap your rows into columns (or vice versa), the
transpose feature lets you simply do this without manually copying and
pasting each individual header. Magic, isn’t it?
To transpose a column into a row (or vice versa), begin by right clicking
it, and select “Copy”. Then, select the cells wheere you want your first
row or column to begin, right-click on that cell, and select “Paste
Special”. A window will pop-up – check the “Transpose” box at the
bottom right and click OK. Now your row is now a column or vice versa.

4. VLOOKUP

If you’re working across different sheets and workbooks, VLOOKUP will


allow you to combine data to create reports and summaries. If you
need to combine data sets, VLOOKUP is your go-to formula. When
using this formula, make sure you have at least one column that
appears identically in both places.

For example, you might have a list of people’s names next to their
email addresses in one spreadsheet, and a list of those same people’s
email addresses next to their school names in another – but you want
the names, email addresses, and company names to appear in one
place.

The magic formula: =VLOOKUP(lookup value, table array, column


number, [range lookup])

5. Quick Analysis
The Quick Analysis feature is one of the newer features in Excel. It used
to take a bit of work to analyze your data but Quick Analysis makes it,
well, quick. You can instantly create different types of charts, including
line and column charts, and sparklines. You can also apply a table
style, creative PivotTables, quickly insert totals, and apply conditional
formatting. In case you’re not sure which one to use, here’s a
breakdown:

 Formatting lets you highlight parts of your data by adding things like
data bars and colors. This quickly lets you see high and low values,
among other things.
 Charts shows you recommended charts based on the data you
have selected.
 Totals lets you calculate the numbers in columns and rows. For
instance, Running Total inserts a total that grows as you add items to
your data.
 Tables make it easy to filter and sort your data.
 Sparklines are tiny graphs that you can show alongside your data.

To access the Quick Analysis menu, select your data and click on the
icon that pops up in the bottom right corner.
6. Autofill
Autofill is a huge time saver that anyone can use. Excel notices
patterns in your dataset and automatically completes the rest. For
instance, if you want to include only even numbers up to 100, you can
start by inserting 2, 4, 6, and 8 into cells, hovering over the bottom right
of your data selection (you should see a black cross), and dragging
downwards until you have the data you need.

7. Conditional formatting
Conditional formatting makes it easy to identify trends, outliers, or get
a general sense of your data at a glance. Specifically, it lets you
change a cell’s color based on the information in the cell. For
example, you can highlight numbers that are below average in your
spreadsheet.
➤ Ready to go digital? We share 10 tips to unlock the potential of Microsoft
OneNote
To apply conditional formatting to your data, first highlight the cells
that you want to apply. Select “Conditional Formatting” in the Home
tab and choose an option from the dropdown menu. A window will
pop-up that’ll prompt you to provide more information about your
formatting rule. Click OK once you’ve done and you should see your
results right away.

8. Dollar signs
You can use dollar signs in a formula to make sure that a specific
column and row stay the same – even if you copy and paste that
formula into other cells. In other words, dollar signs let you change your
Excel formula from a relative one to an absolute one. To make this
change, simply add dollar signs before and after the row and column
values, like this: from (=A5+C5) to (=$A$5+$C$5).

9. Filters

Filters are a great way to look at specific data within a large data set.
With filters, you can reduce your data to only look at what fits into
certain criteria (without changing the original data set). For instance,
in the example below, I might only want to look at students in Ottawa
– filtering lets you do that. In Excel, you can add filters to every column
and then choose which cells you want to view at the same time.
To begin filtering, select your data set, Home > Sort & Filter > Filter.
Arrows will appear next to your headers. Clicking on these will reveal
a drop down menu where you can filter to your heart’s content.

You might also like