Top Microsoft Excel Tips For Cincinnati Business Professionals
For more than three decades, Microsoft Excel has remained the most widely utilized software by businesses. Unfortunately, very few users have realized the full capabilities of Excel. For example, only a few people understand how to use Conditional Formatting, a vital feature of Microsoft Excel. If you need a quick dose of tips on how to improve your Excel proficiency, here are our top 10 tips:
1: Use Pivot Tables to Make Sense of Data
You can use Pivot Tables to reorganize data in a spreadsheet. These tables don’t change the existing data but simply sum up values and compare different parameters in your spreadsheet. To create a Pivot Table, click Data > Pivot Table. Microsoft Excel will automatically populate your Pivot Table, but you can change the order of the data depending on what you want it to do.
Once you’ve created your Pivot Table, you have four options to choose from:
- Report Filter: This allows you to only view certain rows in your dataset.
- Column Labels: Could be your headers in the dataset.
- Row Labels: Could be your rows in the dataset. Both row labels and column labels can contain data from your columns. For example, if you have a list of clients, you might want to drag the First Name to either the Row or Column label.
- Value: This allows you to view your data differently. For instance, instead of pulling in numeric values, you can count sum, max, min, average, or do a few extra manipulations with your data.
2: Add More than One Colum or Row
When analyzing data in excel, you might need to constantly add more rows and columns. Depending on the amount of data you’re dealing with, you may need to add hundreds of rows at a go. However, doing that one by one would be extremely tedious. Luckily, there is a simpler way to add several rows in excel. Just highlight the same number of preexisting columns or rows you want to add, right-click, and press “Insert.”
3: Using Formulas for Simple Calculations
Other than doing complex calculations, Excel can help you execute simple arithmetic such as adding, subtracting, dividing, multiplying, and averaging any of your data.
- To add, click the + sign.
- To subtract, click the – sign.
- To multiply, click the * sign.
- To divide, click the / sign.
If you’re doing multiple calculates, be sure to use parenthesis to dictate what calculations are done first. For example, inputting 5+6*7 means that 6 and 7 are multiplied together before adding 5. However, if we made it (5+6)*7, then the 5 and 6 would be added together first before multiplying the result by 7.
If you want to average a set of numbers, use the formula =AVERAGE(Cell Range), and if you want to sum up a whole column of numbers, use the formula =SUM(Cell Range).
4: Using Conditional Formatting
Conditional formatting allows you to configure a cell’s color based on the data within the cell. For instance, if you want to flag numbers that are above average or fall within a certain range or percentage of the data in your spreadsheet, conditional formatting comes in handy. You can also use this functionality to color code commonalities between various rows in Excel to quickly see information important to you.
To get started, simply highlight the group of cells you wish to color code and choose “Conditional Formatting” in the Home menu and finally select your logic from the dropdown.
5: Use Filters to Simplify Data
When looking at large data sets, you don’t need to look at every single row at an instance. You only want to view data that meets certain criteria, and that’s where filters come in. You can apply filters to pare down your data to only view certain rows at a time. In Excel, you can apply a filter to each column in your data and then choose what cells you want to see at once.
6: Remove Duplicate Data Points
When dealing with larger data sets, you can end up with duplicate content. For example, if you have a list of multiple contacts for a company and only want to see how many companies you have, removing the duplicates can come in handy.
To remove duplicates, simply highlight the column or row that you want to remove duplicates from. Then, navigate to the Data tab, and click “Remove Duplicates” (under Tools). Excel will show a pop-up to confirm the data you wish to work with. Finally, click “Remove Duplicates,” and you’re done.
7: Transpose Rows into Columns
When your spreadsheet has low rows of data, you might want to transform items in one of such rows into columns. Copying and pasting each individual header could take a lot of time. The transpose feature allows you to move your row data into columns or vice versa simply.
To do that, highlight the column you want to transpose into rows, right-click it, and select “Copy.” The next step is to select spreadsheet cells where you want the first row or column to begin. Next, right-click on the cell and click “Paste Special,” and you’ll see a module appearing at the bottom with an option to transpose. Just check that box and select OK, and your column will be transferred to a row and vice-versa.
8: Split Up Text Data Between Columns
If you want to split out data in one cell into two separate cells, excel allows you to do that seamlessly. For example, you may want to separate a client’s full name into first and last names for your email marketing lists. To do that, highlight the column that you wish to split up. Next, select the Data tab and click “Text to Columns.” A small module will pop up with additional information.
First, select either “Delimited” or “Fixed Width.” Typically, “Delimited” applies if you want to break up data in that column based on characters, such as spaces, commas, or tabs. “Fixed Width,” on the other hand, applies if you want to select the exact position on all the columns you want to split up.
Once you’ve selected the right option and happy with the preview, click “Next.” The page will allow you to choose Advanced Formats to allow more customizations. When done, click “Finish.”
9: Use IF THEN Excel Formula For Automation
Instead of counting the number of times a value appears in the spreadsheet, you can use “if then excel formula.” For example, if you have a list of students and want to award 5 points to every student who majors in Math, you don’t need to input 5 next to every student’s name. Simply use the IF THEN Excel formula to dictate that if the student majors in Math, then they should be awarded the 5 points.
10: Use Excel Keyboard Shortcuts
It’s important to learn all of Excel’s keyboard shortcuts to save time and use the tool more efficiently. Here are some common excel keyboard shortcuts to make your work smoother and faster:
- Ctrl+N: Create a new workbook.
- Ctrl+O: Open an existing workbook.
- Ctrl+W: Close a workbook.
- Ctrl+F4: Close Excel.
- Ctrl+S: Save a workbook.
- F12: Open the Save As dialog box.
- F4: Repeat the last command or action. …
- Shift+F11: Insert a new worksheet.
Great Microsoft Excel Services For Cincinnati Business Professionals
At 4BIS.COM, we work with large and small organizations throughout Cincinnati, providing outsourced IT services and IT support. We are a top Microsoft partner in the area and want this excellence to shine through. Our team is invested in your business success and will ensure your technology puts you on the path to realizing your goals. We are your external technology department and support team for a myriad of IT tools and solutions. Contact us today to schedule a consultation with our experienced IT engineer!
