SIX MICROSOFT EXCEL HACKS
MICROSOFT EXCEL HACKS
Microsoft Excel is a powerful tool. It can be used for data analysis, presentation, making models etc. If you understand MS excel well, it can open a new dimension of career options for you.
AUTOSUM ALL SELECTED CELLS
We all get so used to slow, inefficient ways of doing things that we don’t even look for faster ways.
That is why many Excel users don’t use this simple shortcut for autism all selected cells. You can expedite your number-crunching simply by summing numbers in a contiguous range. The shortcuts are given below:
Windows: Alt +=
macOS: Command + Shift+T
FLASH FILL YOUR CELLS
The filling is such a boring and time taking task!!
Well, you can make it faster in MS Excel by using Flash Fill. Flash fill is the fastest filling function. It fills a column based on the pattern of data it sees in the first column (it helps if the top row is a unique header row).
Suppose the first column has dates formatted like “DD-MM-YYYY” and you want them to all look like “MM/DD/YY,” start typing. By the second cell, Excel should recognise the pattern and display what it predicts you want. If the prediction is right then just hit enter to use them.
If the pattern is hard to recognise and the second cell doesn’t give you an accurate range, then type some more and go to the Data tab and click the Flash Fill button.
You can also use this Microsoft tutorial to learn flash filling cells.
PASTE SPECIAL WITH FORMULAS
Paste special can be used to modify formulas and evaluate numbers with operators. Formulas can be bound to other formulas or named ranges with the use of mathematical operators.
Paste Special dialogue can be accessed from the right-click context menu, or by pressing the Ctrl + Alt + V key combination while pasting data. The dialogue box contains all the paste methods in Excel.
If a huge dataset of numbers is in the decimal format but you want to show them as percentages. That’s where Paste Special comes in.
Type 100 in a cell and copy it,
then, select all the numbers you want to be reformatted,
and select Paste Special,
Click the “Divide” radio button,
You’ve got numbers converted to percentages. This also works to instantly add, subtract, or multiply numbers, obviously.
For learning MS Excel in-depth you can also enrol for this course on Scholarbees.
Get Solution at Scholar Bees and find India’s best Online tutors
PIVOT TABLES
Pivot table is arguably the most powerful feature of Excel’A pivot table allows you to extract the significance from a large, detailed data set.
A pivot table is a summary of a large dataset that makes it much easier to parse the information based on certain reference points.
It can be useful for anyone. Suppose you’re a teacher and you have the entire dataset of grades for all your students across all tests for the whole year.
Through a PivotTable, you can narrow down the data to one student for one month or for one specific subject.
For creating a PivotTable,
1. check that all the columns and rows are titled properly.
2. and then select PivotTable on the Insert tab.
3. try the Recommended PivotTables option to check if Excel can pick what’s best for you.
4. You can try the PivotChart, as well. It creates a Pivot Table with a graph to make it easier to understand.
CONDITIONAL FORMATTING
We all do conditional formatting but most of us do it manually. Like colour code the students below 75% attendance with red.
If you apply conditional formatting then most of such colour coding, highlighting, or strikethrough can be done automatically when they meet the criteria set by you.
Excel’s Conditional Formatting can do a lot from putting a border around the highlights to colour-coding the entire table. It can also make a graph into each cell so you can visualize the top and bottom of the range of numbers at a glance. It might be slightly difficult to learn but it is very useful for even laymen users be it teachers, marketers, designers, salesman etc.
Excel provides very advanced conditional formatting options and it allows customisation too. It has a huge library of preset conditions. You can also custom-make conditions and apply multiple conditions as well.
USE $ TO PREVENT SHIFT
Another useful yet simple hack that most people don’t know is the use of $ sign to prevent shifting.
In MS excel upon writing a formula, the reference cells by their position, such as A1. If you copy a formula and paste it in the next cell below, Excel will shift that referenced cell and it would say A2.
Use the dollar sign ($) to prevent shifting. If you type $A$1 and cut and paste it to a new cell the shifting will be prevented.
Let’s take this example:
Say you want to divide everything by 1000. You could do a formula like =(A1/100). But in this case, you won’t be able to change 1000 easily across the sheet if you wished.
So what you can do is put 1000 B1 and use =(A1/B1). But in this case, when you paste it down, it turns to =(A2/B2), then =(A3/B3) so on and so forth. If you use $ sign (A1/$B$1) and then cut and paste in a row, the $B$1 reference will not change and if you wish to change the value of 1000 just go to B1 and change it. Simple.
Join our India’s Best Online Tutors at Scholar Bees
Online tutoring is an effective way of learning.
It allows greater flexibility than online courses.
A better way of online tutoring will help the students to practice their skills in the comfort of their own home.
And also, it will help the student to feel more relaxed and receptive to learning.
These instructors can provide instruction to the students with greater flexibility to address varying learning styles and use different technologies.
Some of the features are helping you to feel more attractive for choosing the best online tutoring websites for your children are:A user-friendlyAn interactive whiteboardChat functionsFile-sharing capabilitiesOnline tutoring is an effective way of learning.
A number of studies regarding school education especially reveal an increasing demand for tuition teachers.
The reasons for that could be several.
Home tuition is one of the most popular forms of teaching that students are going for these days.
It is better than the traditional modes of teaching and learning that are used in schools.
These traditional modes focus more on finishing the syllabus rather than the level of understanding that the students have.
There are numerous reasons why parents choose tutoring for their children.
Some parents do not have advanced study skills in new subjects and they feel unable to help their children with schoolwork.
He can acquire more knowledge from the tutor and get more marks in tests, exams, and any educational-based quiz.Do you also have a need and want to hire the best tutor in London?If you do not have any knowledge about physics, biology, and mathematics and want to hire the best tutor in London who has advanced knowledge of the entire latest syllabus and has great experience in the teaching industry then you arrive at the perfect place.
It is the database for students worldwide seeking private and online tutoring.
It helps people seeking help to find the right tutor for them.
Usually, we have tutors available in all subjects for primary, secondary & high school levels.Our professional staff has advanced knowledge about biology, physics, and mathematics.
