Top Excel Tips and Trick
 |
| Top Excel Tips and Trick - Code To Coding |
Here are some of the most useful tips and tricks for you to help your work better with excel.
1. The best way to use an array
This is a very important tip, especially if you only have one column in your spreadsheet. To simplify this process, just add more columns (as long as there are enough rows) to your worksheet. If you have a few columns, it will be even easier.
2. Add a cell to the sheet
In many cases, we don’t need a whole new line. We can simply add a single word into our cells as shown below.
3. Insert and delete a range
We’ve all seen the “insert as shown in the example above” function on the keyboard, right? There isn’t much difference between that and selecting from a list. It’s pretty easy to remember if the list is large. But when there’s only one column. You can get lost easily.
4. Use multiple sheets
Multiple sheets are not needed. It is hard to think while using two-columns or three-cols. When I first started working with excel, I was used to having them only in one sheet. So I thought if I was going to use two rows, I should use both. That’s what I did till now! As long as you keep one thing in mind and one thing in your data file. In case you want to change the data type, then you need to convert all of your data to one or another. It won’t change and the result should remain the same. So in my opinion, you should only do so if you have one line of data.
5. Selecting a cell and shifting it for a range
The most convenient method is selecting a range and then moving it that way we can highlight it. Just click on it.
6. Using CTRL+V
This is very handy. For editing, sometimes you just forgot where a certain section needs re-visiting. Well, this trick makes it much easier. With this, you can go to that range, select one portion of it, switch the values, and put them back where needed. Now look at the first cell for this range and copy it.
7. Making up by adding and deleting cells
 |
| Top Excel Tips and Trick - Code To Coding |
You want to insert a cell, but you can’t find it for it, right? well, you make another and then return it where you need it. This helps us in finding our missing cells.
8. Pivot Tables
What about pivots? Well, they help show us how things appear when their relationship is changing. More importantly, let us see the graph in different ways. They let us preview how everything looks like when viewed without making any changes. How can we identify whether something has changed if someone else added or deleted something if it is too long to show at once?
9. Getting maximum performance out of files
We’ve heard this many times, and I couldn’t disagree with it more. Whether you know it or not. This is how you can quickly perform tasks in excel, such as opening a document, inserting a field, deleting a particular value, etc. Without thinking about it. Just follow these steps. Then start playing around with the various features in it and reap the benefits.
10. Changing a cell can modify a formula
This is the old saying. “if you didn’t know, ask”. Sometimes it will happen to you and you don’t realize it.
11. Creating Cells and manipulating formulas
Excel provides cells and formulas. If you’re familiar with simple math, then this tip is for you. Just insert a number and then manipulate it. Remember to insert the number and not the range.
12. Changing fields within a cell
Sometimes you forget what a field is. But it’s quite useful and you don’t have to worry when you lose cell references.
13. Showing a figure inside a cell
When data gets complicated, this is an amazing tool for understanding. And it shows you exactly where your data has been manipulated.
14. Separate cells to calculate from
When you have a very complex dataset, it might be tough to visualize the numbers and graphs in your spreadsheet. Maybe you see some graphs and charts in a table but you have no idea where the details lie. Or maybe a cell is too big to display anything. In these cases, you need numbers and graphs to separate your cells to see if there are any issues.
 |
| Top Excel Tips and Trick - Code To Coding |
Sometimes I just forget about these functions. But still, you can take advantage of using them. These are the ones that you find helpful and essential. Try to do this regularly so that you’ll never forget what you’ve been doing.
16. Finding duplicates in a file
If you found some duplicates, you can easily select them in excel. Make sure to write down your answer here, it will become quite tricky.
17. Display a pie chart in a cell, don’t show it on the top
Sometimes you can only get a little bit of detail in your worksheet. Even if you have hundreds, it’s not worth displaying that far.
18. Adding filters
By adding filters, you can group and sort by certain criteria, which means you can sort your data based on the type of field or action done. This helps us to quickly filter by some criteria.
19. Calculating averages
Many times when we’re facing problems, what we normally do is sum up our data. But sometimes data is messy even though we can try. So, to avoid this problem you can also use formulas to group the data as shown below. Let us say that you’re calculating the average income per employee. Then you can simply group this average by each category.
20. Summing numbers in a cells
Sometimes we find that our data is not neatly divided. Or maybe you want a good approximation of the true number and you would rather have it displayed in the form of a percentage rather than a whole number. In these situations, this is a great way to solve it.
21. Formatting a cell
Sometimes we find the correct format for some cells that look bad in excel. The formatting should also fit the requirements of the current situation.
22. Formulas in cells
In a cell formula, you need to add your numbers to a formula, which tells if something is being changed or not. So whenever you add fields or calculations in this case, make sure to specify the formula.
23. VLOOKUP function
This is one of the most versatile functions in excel. No matter in which column, cell or formula you want, you can retrieve information by pressing down the ‘V’ key.
24. Checking a formula
Sometimes you might forget that checking one of these functions is always good. Like VLOOKUP, this might look weird. But if you have to update your documents frequently, then you can use them in your work.
 |
| Top Excel Tips and Trick - Code To Coding |
25. Helping to select figures in a format
Sometimes you find yourself working with more complex figures in files and it can be a hell of a struggle to visualize them. It takes some time to process the formula but eventually you’ll get what you want.
26. Resizing figures in a cell
Sometimes you need more space, sometimes you just need to resize it. But sometimes we can’t decide what will work for us. We should know what should be kept and what should be removed.
27. Adjusting the colour
To improve your work, sometimes it’s helpful to adjust the colour to the context. Some words might seem brighter in red than they usually are, it’s because they relate to something very important. Others can be slightly darker concerning others.
28. Editing data
Most people would use this function only on spreadsheets and spreadsheets where we use basic data types. but for other purposes, it’s absolutely indispensable. Always, be careful when adjusting the colours, otherwise, it will cause errors in your work.
29. Re-setting formats
Sometimes due to the error, for whatever reason, you might lose every function in your file and you cannot control the text. Also, there might be multiple formats and the one you’re using won’t give you any hint.
30. Fixing mismatches in a cell
Sometimes the mistake may occur in more than one place in the body of a cell. If you don’t know how to fix this, that’s one of the easiest ways to save your file. All you have to do is to find the right spot but first, you need to make sure that you are in the same row. Then you will have something to edit.
31. DIFFERENTIZING the content
Sometimes you can see incorrect values. So, when you use DIFFERENTIATION, you’ll notice the errors quickly. Don’t worry about trying to fix the issue and then, later on, you’ll see the mistakes again and again.
32. Searching cells
Sometimes you can’t find anything. So when you want to search for a cell, press the ‘S’. But sometimes you have a lot of them and you can’t reach them in small blocks. So firstly, you have to make sure that you have a table with all of these items of your search. Then go to where you need to search and see which values you have. You can also search directly with the CELP function,