Whether you’re a data engineer, accountant or student, you have likely used Excel. The chances are, you know the basics, but you probably don’t realize just how much you could achieve if you knew the full scope. Excel is Microsoft’s most influential product, with 750 million people using it worldwide. Here are some tips and tricks to help you get the most from your Excel spreadsheets.
How To Make Aesthetic Changes
If you improve the aesthetics of your spreadsheet, you aid the data’s interpretability. Freezing your headings will help the reader know what information they’re reading as they scroll through the document. This function makes the titles always visible on the screen. You can follow this procedure to fix your headings. To do this, go to View > Freeze Panes > Freeze Top Row (freezes column headings), or Freeze First Column (freezes row headings).
Excel offers themes and templates outside of the default Office one. These can help organize your data and make a more appealing spreadsheet. You can also create a theme:
- Page Layout > Fonts (choose your font)
- Page Layout > Colors > Color Palette (choose a built-in or custom color scheme)
- Page Layout > Themes > Save Current Theme (enter a name)
A lesser utilized tool is the Format Painter. The Format Painter duplicates the format used in one cell into another. To use it, click the cell with the desired formatting, click the paintbrush icon in the toolbar, and then move your cursor to the unformatted one, or drag it through a range.
Form Follows Function
You can get summary data without formulas by selecting the data range and looking at the bottom, displaying the average, sum and count. Standard Error in Excel is one of the most important statistical metrics, and allows you to analyze the accuracy of your data sample. You can find it with formulas or the Data Analytics ToolPak.
If you want to create a table quickly, you can highlight the desired data, and press CTRL+T on your keyboard. From there, you can adjust the table by exploring the Table tab. If you want to make a chart, you select the data and press ALT+F1. If you cannot remember the formula you want to use, go to Formulas > Insert Function, and then search through the dialog pop up for the desired function
There are a lot of different ways that you can enhance your spreadsheets. While it may take a bit of getting used to, internalizing the shortcuts and tools is worth it. It’s best to practice each tip carefully to make it a more natural part of your future spreadsheets.