I’m working on a excel project and need support to help me study.Format all datasets (report outputs) as tables.
Report A and Report B need to be combined into one table (the
resulting table should also be formatted as a table). You do not need
two copies of the ID number in the new table, only one. (Hint: you do
not need to create a whole new table; you can append one table to the
other – just be sure to use tools to ensure your records match!)
Ensure your workbook elements are all formatted correctly (i.e., any
monetary elements as currency, numbers with comma separators, etc.),
and adjust font and views so that they are readable and presentable. As
you create new columns, ensure that all columns are labeled logically
(i.e., do not leave any as “Column1”, etc.).
Sort your resulting table based on ID number, smallest to largest,
then on professional group, A to Z, then on income, smallest to largest
(do not sort these independent of each other using the table filter
buttons; rather, sort these as building steps).
Insert three new columns in your table and 1) use a function to
create a date view of the record’s start date, 2) use a function to
create a date view of the record’s end date provided OR use the current
date as the end date, 3) calculate the number of days between the
record’s end date.
Insert a new column in your table and categorize each record based on the information presented in the “Group_References” tab.
Create conditional formatting for income in your resulting table, using green, solid fill data bar formatting.
Group the address information except for the ZIP code and the
original date elements (the individually listed month, day, year fields)
in the combined report so that they can be collapsed. (Note: the
address information can be one grouping, and the date fields a second
grouping; all these fields DO NOT need to be grouped as one set.)
On the “Summary_Sheet” tab, follow the instructions in the right-hand column to complete the sheet.
Create a PivotTable of your resulting table. Create a view which is
meaningful and interesting to you, conveying information which you would
want to present.
Create three different visuals, each a different type, based on the
data in your resulting table. These are open-ended and should reflect
your own interpretation of the data. Visuals should be appropriate for
the type of data you’re analyzing, well-formatted, and follow best
practices. You can even use your PivotTables to help with this task by
pivoting the data, then creating visuals based on those results (hint:
once you have your data pivoted as you’d like, copy just the
PivotTable’s row, column, and values data, paste it elsewhere, and
create your visual from there).
Requirements: N/A
