Blog 7: Using Excel – Formatting Data Into Tables
Using Excel: How to Convert Data into Tables and Back!
Microsoft Excel, as part of the larger Microsoft Office suit is an excel business tool. In my work, I have found it effective for developing budgets, time, and labor estimations, and even some script-kiddie programming! But today the topic is a simple how-to?
- Open Excel.
// If you’re using a Windows 10 OS Press [ Windows + R], this will open the Windows Run feature as possibly say you time! Once open type excel.exe to create a new instance.
- Press [Ctrl + S] to Title and Save the document, and then select a location on your laptop.
// Titling the document can be as simple or as complex as you’d like to make it but ensure that it reflects the data types included. For example, ‘1 Month Financial Budget’.
- Next, we title our headers. The documents will always open to field A1 and have a green box around it is indicating the ACTIVE field. Here we’re going to name this Budget Categories.
- Our next header will be place in B1 named Estimated Monthly Cost.
- With our headers completed we need to flesh out the footers with more strings, what sort of things do you spend money on broadly speaking? Food, Rent or Mortgage, Transportation, Utilities? It’s important to get the biggest expenses down first, for most people this is going to be just what I mentioned.
// Remember, if this is your first-time budgeting, estimations are a big part of the process – and that to make them accurate attention to detail, and even some external data sources, are necessary. With that let look at some statistics to get an idea for one volatile price-points, Food. Well Kept Wallet was my source document for the food estimate, of $405.30. I placed that in category B2. Next, we need some data on Rent OR Mortgage, for someone with a Mortgage this value should be easily gotten. In my circumstance, Louisville Rent Café’ has generated an estimate of $1,034 per month, this is in B3! That’s a lot of labor! Social studies aside, let’s enter the data. Transportation, once locked in, is another large and static expense for most people – and if only I could have stopped myself in my mid 20s! This value varies from household to household, but AAA’s data suggest the price to be roughly $713 per month, that’s in B4! For a labor perspective, no wonder people are protesting about the cost-of-living! The last category we’re going to look at is Utilities, which in my mind are a combination of Electric, and Internet – some rental agreements will cover others such as garbage and water. Numbeo estimates this value to be $199.00, finally that’s in B5.
Once we have placed this semi-unformatted data down, we are going to add units of measurement as a nice table. For a table of this size, the sorting features of the table won’t be much help, but this is a beginner-level article!
- To place the data and its headers into a nice grouping, left click and select all the relevant cells.
- View the Styles Ribbon at the top of the document, and Select ‘Format as Tables’, select any design and then select OK.
// The last thing is to format the data in the ‘Estimated Monthly Cost’ column into currency.
- Again, select all relevant data with left click. This will be B2:B5.
- Right-click and select format cells. This will bring up a window titled ‘Format Cells’
- Select Currency and Select OK. The default is to show a two-point decimal for cents.
That’s it, you’ve successfully estimated and formatted the type and cost for a month of some bills! You may find that Tiller Money is a good alternative, if you prefer to use pre-formatted masks – but there are some pros and cons with that system that I will discuss later.
The final quick thing to discuss is how to unformat this simple data.
- Right click any cell in the range
- Select Table in the drop-down
- Select Convert to Range
- Done!
Thanks for reading! If you’d like to discuss further technologist concepts shoot me an email!