The Biggest Mistakes You're Probably Making In Microsoft Excel
Tracking, organizing, and displaying data is crucial for business, finances, and personal goals. Microsoft Excel has been the go-to application for decades with its straightforward user interface and comprehensive features. Microsoft didn't invent the electronic spreadsheet; that title belongs to Dan Bricklin, who in 1979 developed VisiCalc, which would go on to inspire Excel. Nowadays, beyond basic data logging, Excel offers comprehensive options that include complex macros and interactive dashboards. According to Earth Web, upwards of between 1.1 and 1.5 billion Excel users exist worldwide. Yet many people may not be aware of some basic features and, worse still, may even be doing some tasks incorrectly.
Common errors can prevent the desired results when importing data, changing existing spreadsheet information, or implementing more complex features such as formulas. You may have used Excel for years, but since most workplace training on the application is limited, you might inadvertently have made your daily tasks more difficult.
To better understand Excel and improve your productivity, you need to avoid some prevalent missteps. These mistakes include incorrect formatting, unnecessary file sizes, and incorrect file types. To get the most out of the application, you should know the most helpful Microsoft Excel shortcuts to improve your experience.
Export to .xls or .xlsx file format?
One of the first issues you might encounter working with Excel is when exporting data from another application or source into Microsoft's software. Other programs often allow you to export to either .xls or .xlsx format when moving the data to Excel, and Microsoft's application will work with either. However, these formats are vastly different and will influence your experience in Excel once the data is transferred.
The .xls is an older file format that Microsoft used before 2007 and doesn't offer the same number of features. The .xls format is based on a Microsoft in-house binary that can't interpret XML rules and encoding. Conversely, the .xlsx is the preferred format because it's based on the current universal application standard, Office Open XML, meaning it will play nicer with other applications.
In addition, .xlsx is a compressed file format, meaning the .xls format creates larger file sizes for every sheet. So, by selecting the older .xls, you are using up more storage space for the file than you would be when selecting .xlsx. Lastly, the older file format supports fewer rows and columns than the newer .xlsx. For example, with .xls, your final row is limited to 65536, whereas .xlsx can support up to 1048576 rows for substantially more data.
Are your dates formatted as dates?
Another easy mistake when working in Excel is to include dates formatted as text. Again, this can happen when importing from another program into Excel, which creates problems. When Excel sees a column of dates as text, the application doesn't interpret them correctly when you start to implement formulas into the sheet. So, if you're having issues getting a formula to work correctly, you should verify Excel is reading your data as dates and not as text.
If you want to verify your dates are formatted correctly, there are a few simple methods to try. First, later versions of Microsoft's application can detect formatting errors and might display an icon notifying you of the discrepancy. Error messages are handy when you only have a few dates incorrectly formatted, but what about an entire column's worth? In this case, you can use a function called Format Cells, which allows you to select large amounts of data and then adjust how it's interpreted. Once you gain a deeper understanding of Microsoft's application, you can start to incorporate Excel hacks that will make spreadsheets less stressful.