In this blog, we will check 10 tips for Excel by BPA Educators.

Ms excel is an application by Microsoft that helps people get their work done easily and quickly.

10 tips for excel

CONDITIONAL FORMATTING – Tips for Excel

The most useful Excel formula is conditional formatting in Excel 2019. With three simple clicks, you will be doing most of your tasks in official work.

Steps to use conditional formatting

A cell changes colour depending on the number entered into it.

B. Conditional formatting – user-defined rules are changing the colour.

C. Error checking – Excel is automatically spotting problematic data entries.

PIVOT TABLES

If you want to look only at sales figures for specific countries, product lines or marketing channels, it’s trivial.
The steps for the pivot table are as follows:
A. They are a way of allowing users to enter data into Excel
B. They are a set of formatting templates for data
C. This will summarise the data tables flexibly.

PASTE SPECIAL (i.e. Copying) 

Some data from one cell and pasting it into another cell is one of the most common activities in Excel. But there’s a lot you might copy (formatting, value, formula, comments, etc.), and sometimes you won’t want to copy all of it. The most common example of this is where you want to lose the formatting – the place this data is going is your own spreadsheet with your own styling. It’s annoying and ugly to plank in formatting from elsewhere. So copy the values, and all you’ll get is the text, number, whatever the value is.

The shortcut after copying the cell (Ctrl C) is Alt + Ctrl V – easier to do than it sounds. The other big one is Transpose. This flips rows and columns around in seconds. 

ADD MULTIPLE ROWS 

Probably one of the most frequently carried out activities in spreadsheeting. Ctrl Shift + is the shortcut, but it takes longer than just right-clicking on the row numbers on the left of the Excel display. So Right Click is our recommendation. And if you want to add more than one, select as many rows or columns as you’d like to add and then Right Click and add. 

ABSOLUTE REFERENCES

 ($) Indispensable! The dollar in front of the letter fixes the column; the dollar sign in front of the number fixes the row F4 toggles through the four possible combinations. Try it out with the following exercise. Type out three foods horizontally in cells B1, C1, and D1 (Olives, Granola, Tomatoes) and three colours in cells A2, B2, and C2 (Green, Blue, Yellow). Now type in cell B2 ‘=A2&” “&B1’. Congratulations: Green Olives! Now – and here’s the exercise – add dollar signs so that when you copy the formula across, you get everything green. Or just Granola, but of different colours. Experiment!

PRINT OPTIMISATION 

Everyone has problems printing from Excel. But imagine if what you printed were always just what you intended. It IS possible. But there are a few components: print preview, fit to one page, adjusting margins, print selection, printing headers, portrait vs landscape and spreadsheet design. Invest the time to get comfortable with it. You’ll be carrying out this task many times in your working life. Which Excel ‘View’ gives the most unambiguous indication of how a Worksheet will print?

Normal View

Reading View

Page Break Preview

EXTEND FORMULA ACROSS/DOWN  tips for Excel

The best part of Excel is its easy scalability. Get the formula right once; Excel will churn out the correct calculation a million times. The + cross handle is handy. Double-clicking it will take it down if you have continuous data. Sometimes a copy and paste (either regular Paste or paste formulas) will be faster for you.

FLASH FILL – Excel

The new version of Excel is 2016.
If you have two columns of names, you need to construct email addresses from them. Just do it for the first row, and Excel will work out what you mean and do it for the rest. Pre-2013 this was possible but relied on a combination of functions (FIND, LEFT, &, etc.).
Now, this is much faster and WILL impress people. Flash Fill should start working as you type if Flash Fill is turned on (File Options Advanced). Or get it going manually by clicking Data > Flash Fill or Ctrl E.

INDEX-MATCH  tips for Excel

It is one of the most potent combinations of Excel functions. You can look up a value in a big data table and return a corresponding value. Let’s say your company has 10,000 employees, and there’s a spreadsheet with lots of information about them like salary, start date, line manager etc. But you have a team of 20, and you’re only really interested in them. INDEX-MATCH will look up the value of your team members (these need to be unique, like email or employee number) in that table and return the desired information for your team. It is worth getting your head around this as it is more flexible and, therefore, more potent than VLOOKUPs.

FILTERS tips for Excel

Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually, there’s a value, e.g. ‘Blue cars’, that you’re looking for, and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now filter on number values (e.g., more significant than the top 10%, etc.) and cell colour. Filtering becomes more powerful when you screen more than one column in combination, e.g., colours and vehicles, to find your blue car. Alt D F F is the shortcut (more accessible than it sounds – give it a go). Conditional Formatting and Sorting serve related purposes. Sorting involves rearranging your spreadsheet, which is intrusive and may not be desirable. Conditional formatting brings visualisation. Filtering is fast and effective. Choose well.

Also, check out the MS excel course training at Bpaeducators and become an expert in Excel. 

Tally Accounting Software Course

Batch Launch

Tally

Tally

Many businesses use Tally accounting software. It is also the basic need of any business. Moreover, BPA Educators will teach you these job-orientation courses from the basics to the advanced levels over 60 hours.

Know More

MS Excel

Excel MS Excel / Advance Excel

BPA Educators offers courses in Microsoft Excel in order to help you keep track of numbers and analyze them correctly. Moreover, BPA Educators offers this course as one of its most popular job-orientation courses.

Know More

GST

GST

GST is a comprehensive tax raised on manufacturers, sellers, and also the consumption of goods and services across India. We also teach you all tax concepts.

Know More

What do our Students say?

If you are looking for English-speaking classes, I would suggest you go to the BPA Educators Institute. Further, I really had a great experience. It also boosts my confidence, enhances knowledge, and improves my speaking skills.

Testimonial

Pooja Mishra

Best institute ever….. The staff and teachers are brilliant… I am also a student who is studying here……..The regular test is been taken for the betterment of an individual.

Testimonial

Harris jayaraj

My brother studying in a repeater batch. I am very much satisfied and happy the way they guide my brother. A big thank you to BPA Educators who always support and cooperative with us.

Testimonial

Payal

×

Support Team

Ask us anything. We would love to hear from you.

×
New Batches getting launched | Free career counselling session for all the students| Free assessment for 12 class students | Spelling Bee and vocabulary Bee competition for all                         New Batches getting launched | Free career counselling session for all the students| Free assessment for 12 class students | Spelling Bee and vocabulary Bee competition for all                         New Batches getting launched | Free career counselling session for all the students| Free assessment for 12 class students | Spelling Bee and vocabulary Bee competition for all                         New Batches getting launched | Free career counselling session for all the students| Free assessment for 12 class students | Spelling Bee and vocabulary Bee competition for all                         New Batches getting launched | Free career counselling session for all the students| Free assessment for 12 class students | Spelling Bee and vocabulary Bee competition for all                         New Batches getting launched | Free career counselling session for all the students| Free assessment for 12 class students | Spelling Bee and vocabulary Bee competition for all                         New Batches getting launched | Free career counselling session for all the students| Free assessment for 12 class students | Spelling Bee and vocabulary Bee competition for all                         New Batches getting launched | Free career counselling session for all the students| Free assessment for 12 class students | Spelling Bee and vocabulary Bee competition for all